JOINS in SQL: INNER, OUTER, LEFT, RIGHT, CROSS

What are JOINS?

Joins help retrieving data from two or more database tables. The tables are mutually related using primary and foreign keys.

Types of joins

Cross JOIN, INNER JOIN ,Outer JOINs ,LEFT JOIN ,RIGHT JOIN

Practice Sample

Cross JOIN

Cross JOIN is a simplest form of JOINs which matches each row from one database table to all rows of another.

In other words it gives us combinations of each row of first table with all records in second table.

Suppose we want to get all member records against all the movie records, we can use the script shown below to get our desired results.

SELECT * FROM `movies` CROSS JOIN `members`

Results

INNER JOIN

The inner JOIN is used to return rows from both tables that satisfy the given condition.

Suppose , you want to get list of members who have rented movies together with titles of movies rented by them. You can simply use an INNER JOIN for that, which returns rows from both tables that satisfy with given conditions.

SELECT members.`first_name` , members.`last_name` , movies.`title`
FROM members ,movies
WHERE movies.`id` = members.`movie_id`

Result

Note the above results script can also be written as follows to achieve the same results.

SELECT A.`first_name` , A.`last_name` , B.`title`
FROM `members`AS A
INNER JOIN `movies` AS B
ON B.`id` = A.`movie_id`

Outer JOINs

MySQL Outer JOINs return all records matching from both tables .

It can detect records having no match in joined table. It returns NULL values for records of joined table if no match is found.

LEFT Outer JOIN

Assume now you want to get titles of all movies together with names of members who have rented them. It is clear that some movies have not being rented by any one. We can simply use LEFT JOIN for the purpose.

The LEFT JOIN returns all the rows from the table on the left even if no matching rows have been found in the table on the right. Where no matches have been found in the table on the right, NULL is returned.

SELECT A.`title` , B.`first_name` , B.`last_name`
FROM `movies` AS A
LEFT JOIN `members` AS B
ON B.`movie_id` = A.`id`

Result

You can see that in the returned result which is listed below that for movies that are not rented, member name fields are having NULL values. That means no matching member found members table for that particular movie.

Note: Null is returned for non-matching rows on right

RIGHT Outer JOIN

RIGHT JOIN is obviously the opposite of LEFT JOIN. The RIGHT JOIN returns all the columns from the table on the right even if no matching rows have been found in the table on the left. Where no matches have been found in the table on the left, NULL is returned.

In our example,  let’s assume that you need to get names of members and movies rented by them. Now we have a new member who has not rented any movie yet

SELECT  A.`first_name` , A.`last_name`, B.`title`
FROM `members` AS A
RIGHT JOIN `movies` AS B
ON B.`id` = A.`movie_id`

Result

Note: Null is returned for non-matching rows on left

Hope you find this use-full , please do share and leave your feedback in comment section.

More Imp Topics

How Looker Writes SQL?

How to calculate percent of total in looker?

Table Calculations in Looker

What are Derived tables in Looker?

How to set conditional formatting in LOOKER?

Looker Interview Questions And Answers

New Looker Performance Recommendations Dashboard

Looker Git Version Control: How To Revert To A Specific Commit In Looker, No Git Commands Necessary

_____________________________________________________________________________________________

Reference :

About Me:-
I am Om Prakash Singh – Data Analytics Consultant , Looker Consultant , Solution Architect .
I am Highly analytical and process-oriented Data Analyst with in-depth knowledge of database types; research methodologies; and big data capture, manipulation and visualization. Furnish insights, analytics and business intelligence used to advance opportunity identification.

You’ve got data and lots of it. If you’re like most enterprises, you’re struggling to transform massive information into actionable insights for better decision-making and increased business results.
Reach out to us if you are interested to evaluate if Looker is right for you or any other BI solution.