SQL Inner Join, (Left, Right and Full Outer Join)

To query data from a single table we usually use the select statement in SQL, but sometimes we might want to query data from more than one table. In this article, we will see how we can do that with inner join in SQL, but also we will look at the full join, right join, and left join in SQL to query data from two or more tables.

Note: To query data from a single table we usually use the SELECT * FROM table_name statement.

SQL Inner Join

To query data from two or more tables we must use a process that will link both tables together. The process of linking is called joining. SQL provides more than one kind of joins such as inner join, left join, right join, full join, etc, but now we ill focus on inner join in SQL.

The inner join links two (or more) tables by a relationship between two columns. Whenever you use the inner join clause, you normally think about the intersection between both tables (in case you have two). To understand the concept of inner join in SQL we will look at a simple example:

Here we have two tables A and B, table A has four rows: 1, 2, 3, 4 and table B has also four rows: 3, 4, 5, 6. When table A joins with table B, we have a result of the intersection of table A and table B which is: 3, 4. The inner join clause finds each row in table A that matches each row in table B, all rows that match are included in the final result.

We use the following syntax in SQL to inner join table A with table B.

SELECT A.n FROM A INNER JOIN B ON B.n = A.n;

As we see in the SQL code example above, we are using the INNER JOIN clause after the FROM clause. The condition to match between both tables A and B is specified after the ON keyword. This condition is called join condition, B.n = A.n.

We can use the INNER JOIN clause to join three or more tables as long as they have relationships between them, typically foreign key relationship is used.

SELECT A.n FROM A INNER JOIN B ON B.n = A.n INNER JOIN C ON C.n = A.n;

Here we have inner join on three tables.

Inner Join in SQL Example Two

Let's see one more example of the inner join in SQL. We will use the users and locations table to demonstrates how the inner join in SQL works.

Each user belongs to only one location while each location can have more than one user. The relationship between the users and the locations table is one-to-many. The location_id column in the user's table is the foreign key column that links the users to the locations table.

To get the information of the location id 1 and 2, we use the following statement.

SELECT location_id, location_name FROM locations WHERE location_id IN (1, 2);

As you can see we are using the IN in the WHERE clause to get the rows with location_id 1 and 2. To get the info of users from the locations with id 1 and 2, we use the following query:

SELECT first_name, last_name, location_id FROM users WHERE location_id IN (1, 2) ORDER BY location_id;

And if we want to combine data from both tables, we can use an inner join clause like the following query:

SELECT first_name, last_name, locations.location_name FROM users INNER JOIN locations ON locations.location_id = users.location_id WHERE users.locations_id IN (1, 2);

SQL Left Join

Left join in SQL is useful when we want to link tables together but the result is different from the inner join. Let's look at the example of left join in SQL:

Here we also have the same two tables as before, A and B. When table A left joins with table B, we have a result of every row in table A and the matched records from table B, which is: 1, 2, 3, 4.

The following syntax in SQL is used to left join table A with table B.

SELECT A.n FROM A LEFT JOIN B ON B.n = A.n;

We have the LEFT JOIN clause after the FROM clause. The condition that is after the ON is called the join condition B.n = A.n.

SQL Right Join

The right join in SQL is almost the same as the left join with minor differences.

We are using the same two tables again, A and B. When table A right joins with table B, we have a result of every row in table B and the matched records from table A, which is: 3, 4, 5, 6. Here the difference from the left join is that we are getting the opposite table.

The syntax for right join in SQL looks like this.

SELECT A.n FROM A RIGHT JOIN B ON B.n = A.n;

We have the RIGHT JOIN clause after the FROM clause. The condition that is after the ON is called the join condition B.n = A.n.

Note: In both left join and right join we get the matched records, but the difference is that in left join we have every column from table A, and in right join, we have every column from table B.

SQL Full Outer Join

The full outer join (full join) includes every row from the joined tables whether or not it has the matching rows. If the rows in the joined tables do not match, the result set of the full join will contain NULL values for every column of the table that doesn't have a matching row. For the matching rows, every single row that has the columns populated from the joined table will be included in the result set.

Note: Full outer join and outer join in sql are the same.

The syntax for the full outer join looks like this:

SELECT column_list FROM A FULL OUTER JOIN B ON B.n = A.n;

We have the FULL OUTER JOIN clause after the FROM clause. The condition after the ON is called the join condition B.n = A.n.

Joining in SQL is very important in many situations when we are working with more than one table, and it is very useful to know about it. If you find this article useful please share it.

Note: If you want to learn more about SQL, I highly recommend you to read this book: SQL in 10 Minutes, Sams Teach Yourself (4th Edition).