SQL JOIN Statements

tags: #sql/basic_statements

What are joins?

A JOIN clause is used to combine rows from two or more tables, based on a related column between them in a SELECT statement. This is followed by an ON clause to specify how two tables are related to each other and helps in identifying the matching rows from both tables that need to be joined.

This reconnects rows that have foreign keys with the corresponding data in the table that the foreign key points to.

There are 4 types of joins in SQL:

• (INNER) JOIN: Returns records that have matching values in both tables (in set theory: INTERSECTION)
• LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table
• RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table
• FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table (in set theory: UNION)

SQL Joins

Inner Join Syntax ("Natural Join")

The INNER JOIN keyword selects records that have matching values in both tables.

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

Note: The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns. If there are records in table that do not have matches in the second table, these records will not be shown.

Left Join Syntax

The LEFT JOIN keyword returns all records from the left table (table1), and the matching records from the right table (table2).

The result is 0 records from the right side, if there is no match.

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

Right Join Syntax

The RIGHT JOIN keyword returns all records from the right table (table2), and the matching records from the left table (table1).

The result is 0 records from the left side, if there is no match.

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

Full Join Syntax

A FULL JOIN, also known as a full outer join, returns all the rows from both tables, including the rows that do not have a match in the other table.

• Note: If there is no match, the join returns a row for each table, with the missing values in the other table set to NULL.

SELECT column_name(s)
FROM table1
FULL JOIN table2
ON table1.column_name = table2.column_name;