JOIN with foreign keys

11 Oct 2023

joins

In SQL, the JOIN operation is used to combine rows from two or more tables based on a related column between them. This is commonly done using foreign keys, which establish a relationship between tables.

When performing a JOIN operation, there are different types of JOINs to choose from, including INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN. These JOIN types determine how the matching rows will be displayed in the result set.

INNER JOIN

The INNER JOIN returns only the rows from the tables that have matching values based on the specified foreign key column. It combines the rows that meet the join condition.

The syntax for INNER JOIN is as follows:

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

LEFT JOIN

The LEFT JOIN returns all the rows from the left table and the matching rows from the right table, where the join condition is met. If there is no match, NULL values are returned for the columns of the right table.

The syntax for LEFT JOIN is as follows:

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

RIGHT JOIN

The RIGHT JOIN returns all the rows from the right table and the matching rows from the left table, where the join condition is met. If there is no match, NULL values are returned for the columns of the left table.

The syntax for RIGHT JOIN is as follows:

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

FULL JOIN

The FULL JOIN returns all the rows from both tables, regardless of whether there is a match based on the join condition. If there is no match, NULL values are returned for columns of the table that lacks a match.

The syntax for FULL JOIN is as follows:

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

By utilizing JOIN operations with foreign keys, you can easily combine data from multiple tables based on their relationships. Understanding the different types of JOINs allows you to retrieve the desired data efficiently.

#sql #joins