sq10

The SQL "join" refers to using the JOIN keyword in a SQL statement in order to query data from two tables.

When you perform a SQL join, you specify one column from each table to join on. These two columns contain data that is shared across both tables.

You can use multiple joins in the same SQL statement to query data from as many tables as you like.

Join Types

Depending on your requirements, you can do an "inner" join or an "outer" join. These are different in a subtle way

Inner join

This will only return rows when there is at least one row in both tables that match the join condition.

Left outer join (or left join)

This will return rows that have data in the left table (left of the JOIN keyword), even if there's no matching rows in the right table.

Right outer join (or right join)

This will return rows that have data in the right table (right of the JOIN keyword), even if there's no matching rows in the left table.

Full outer join (or full join)

This will return all rows, as long as there's matching data in one of the tables.

Join Syntax

Inner Join:

SELECT * FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name = table_name2.column_name;

Left Join:

SELECT * FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name = table_name2.column_name;

Right Join:

SELECT * FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name = table_name2.column_name;

Full Join:

SELECT * FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name = table_name2.column_name;

Example Inner Join Statement

SELECT * FROM Individual
INNER JOIN Publisher
ON Individual.IndividualId = Publisher.IndividualId;

Note: We could use table aliases instead of the full table name. This will keep our statement shorter. For example:

SELECT * FROM Individual AS Ind
INNER JOIN Publisher AS Pub
ON Ind.IndividualId = Pub.IndividualId;

The next few lessons cover each type of join and show examples of usage.