SQL Joins

You’ll nearly always need to connect many tables if you want to extract anything useful out of data. A join clause in SQL joins columns from one or more tables into a new table, similar to a join operation in relational algebra.

In this article, let’s see how the joins work in SQL.

We are going to explore the following SQL Joins.

  ∘ 1 — (INNER) JOIN
  ∘ 2 — LEFT (OUTER) JOIN
  ∘ 3 — RIGHT (OUTER) JOIN
  ∘ 4 — FULL (OUTER) JOIN AND UNION
  ∘ When to use it?

Also, I’m going to use the following tables for the above-mentioned SQL Joins.

1 — (INNER) JOIN

Returns records with values in both tables that are the same.

Image from Author

As long as the condition is met, the INNER JOIN keyword selects all rows from both tables. This keyword will produce a result-set by merging all rows from both tables that satisfy the criteria, i.e. the common field’s value will be the same.

Syntax:

SELECT table1.column1,table1.column2,table2.column1,….
FROM table1 
INNER JOIN table2
ON table1.matching_column = table2.matching_column;

table1: First table.
table2: Second table
matching_column: Column common to both the tables.

What happens?

SQL initially generates a new table containing the columns from both tables you’re combining. The ON statement then looks for values that match between the columns you provide. By preceding the table name with a period, SQL can see which two columns of the tables it will be looking for matches between. SQL then checks for a match in the first table’s first value of the specified column, then in the second table’s first value of the specified column.

If there is a match, the data from both the first table’s row and the second table’s row is copied and pasted into the newly formed table. SQL will not add any rows to the table that do not match.

Another thing to keep in mind is that SQL joins the rows whenever there is a match. As a result, if the data in the columns you’re joining on isn’t unique, duplicate data will appear in the final table.

The query we have to execute for our problem is

SELECT * FROM `customer` c INNER JOIN `salesman` s ON c.`salesman_id`=s.`salesman_id`;

The output

What if there are non-unique values in any of these tables?

Non Unique data in the Second table

Non Unique data in the First table

2 — LEFT (OUTER) JOIN

All records from the left table are returned, as well as the matching records from the right table.

Image from Author

This join returns all rows from the table on the left side of the join, as well as matching rows from the table on the right. The result-set will include null for the rows for which there is no matching row on the right side. LEFT OUTER JOIN is another name for LEFT JOIN.

Syntax:

SELECT table1.column1,table1.column2,table2.column1,….
FROM table1 
LEFT JOIN table2
ON table1.matching_column = table2.matching_column;

table1: First table.
table2: Second table
matching_column: Column common to both the tables.

What happens?

In the same manner that an INNER JOIN detects matches and adds them to a newly formed table, this query does the same. 

The query we have to execute for our problem is

SELECT * FROM `customer` c LEFT JOIN `salesman` s ON c.`salesman_id`=s.`salesman_id`;

The output

The LEFT table, on the other hand, is treated quite differently by SQL (first table). It will still add any rows from the first (or LEFT) table that do not have a match to the new table and insert nulls for the columns from the other table.

What about this?

Image by Author

We have to add some extra words to our query like this.

SELECT * FROM `customer` c LEFT JOIN `salesman` s ON c.`salesman_id`=s.`salesman_id` WHERE s.`salesman_id` IS NULL

In our example, we won’t get any outputs.

3 — RIGHT (OUTER) JOIN

Returns the matched records from the left table, as well as all records from the right table.

Image from Author

The RIGHT JOIN function is analogous to the LEFT JOIN function. This join retrieves all rows from the table on the right side of the join, as well as matching rows from the table on the left. The result-set will include null for the rows for which there is no matching row on the left side. RIGHT OUTER JOIN is another name for RIGHT JOIN.

Syntax:

SELECT table1.column1,table1.column2,table2.column1,….
FROM table1 
RIGHT JOIN table2
ON table1.matching_column = table2.matching_column;

table1: First table.
table2: Second table
matching_column: Column common to both the tables.

This performs exactly as LEFT (OUTER) JOIN but it considers the right table.

The query we have to execute for our problem is

SELECT * FROM `customer` c RIGHT JOIN `salesman` s ON c.`salesman_id`=s.`salesman_id`;

The output

What about this?

Image from Author

We have to add some extra words to our query like this.

SELECT * FROM `customer` c RIGHT JOIN `salesman` s ON c.`salesman_id`=s.`salesman_id` WHERE s.`salesman_id` IS NULL

In our example, we won’t get any outputs.

4 — FULL (OUTER) JOIN AND UNION

When there is a match in either the left or right table, this function returns all records.

Image by Author

The result-set of FULL JOIN is created by merging the results of both LEFT JOIN and RIGHT JOIN. All of the rows from both tables will be included in the result-set. The result-set will contain NULL values for the rows for which there is no match.

Syntax:

SELECT table1.column1,table1.column2,table2.column1,….
FROM table1 
FULL JOIN table2
ON table1.matching_column = table2.matching_column;

table1: First table.
table2: Second table
matching_column: Column common to both the tables.

We can’t apply this in our MySQL example, but let’s see an example of how it is intended to work.

Tables are

Student

StudentCourse

The query we have to execute for this problem is

SELECT Student.NAME,StudentCourse.COURSE_ID 
FROM Student
FULL JOIN StudentCourse 
ON StudentCourse.ROLL_NO = Student.ROLL_NO;

What to do in MySQL?

We have to unify both left and right join. Or else, we can use UNION.

UNION JOIN

Syntax:

SELECT *
FROM table1
UNION ALL
SELECT *
FROM table2

table1: First table.
table2: Second table

What happens?

There are a few things to think about before executing a UNION. Both tables must have the same number and arrangement of columns. The data types of the columns that are going to be merged must be the same. You may choose which column(s) you wish to the union, and in this example, we’ll only union the Name column from both tables because they’re both texts. If the columns specified do not have the same data type, an error will occur.

5 — CROSS JOIN

A cross join creates a table that contains all possible combinations of your tables’ rows. This can result in massive tables, so proceed with caution.

Cross join does not look for any matches between the two data sets’ values. Instead of adding each row from the first table to the final table one by one, each row from the second table will be attached to it and added to the final table one by one.

Syntax:

SELECT * 
FROM table1
CROSS JOIN table2

table1: First table.
table2: Second table

What happens?

A simple example

When to use it?

To help understand, Let’s think about the different questions they are asking. Let’s assume we have two tables with names of students and number of friends they have on Facebook and the number of connections they have on LinkedIn.

1. CROSS JOIN: How many combinations of friends and connections do I have?
2. UNION: How many friends do my Facebook friends have and how many connections do my LinkedIn connections have?
3. LEFT join: How many friends and connections do my Facebook friends have? (Regardless of if they are on LinkedIn)
4. RIGHT join: How many friends and connections do my LinkedIn connections have? (Regardless of if they are on Facebook)
5. INNER join: How many friends and connections do my friends who are on both Facebook and LinkedIn have?
6. FULL OUTER join: How many friends and connections do my Facebook friends or LinkedIn connections have?

Hope this can help. Share your thoughts too.