In SQL, joins are used to combine rows from two or more tables based on a related condition between them. There are several types of joins, but the most important ones are: INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.
Next, I’ll show you how to use each one with examples.
1. INNER JOIN
The INNER JOIN returns only the rows that have a match in both tables.
Syntax:
SELECT columns FROM table1 INNER JOIN table2 ON table1.column = table2.column;
Example:
Let’s say we have two tables: customers and orders. We want to get the names of the customers and the orders they have placed.
SELECT customers.name, orders.product FROM customers INNER JOIN orders ON customers.id = orders.customer_id;
2. LEFT JOIN
The LEFT JOIN returns all rows from the left table and the matching rows from the right table. If there are no matches, it returns NULL in the columns from the right table.
Syntax:
SELECT columns FROM table1 LEFT JOIN table2 ON table1.column = table2.column;
Example:
We want to get all customers, even those who have not placed any orders.
SELECT customers.name, orders.product FROM customers LEFT JOIN orders ON customers.id = orders.customer_id;
3. RIGHT JOIN
The RIGHT JOIN returns all rows from the right table and the matching rows from the left table. If there are no matches, it returns NULL in the columns from the left table.
Syntax:
SELECT columns FROM table1 RIGHT JOIN table2 ON table1.column = table2.column;
Example:
We want to get all orders, even those that do not have an associated customer.
SELECT customers.name, orders.product FROM customers RIGHT JOIN orders ON customers.id = orders.customer_id;
4. FULL JOIN
The FULL JOIN returns all rows when there is a match in one of the tables. If there are no matches, it returns NULL in the columns from the table that has no match.
Syntax:
SELECT columns FROM table1 FULL JOIN table2 ON table1.column = table2.column;
Example:
We want to get all customers and all orders, including those that do not have a match in the other table.
SELECT customers.name, orders.product FROM customers FULL JOIN orders ON customers.id = orders.customer_id;
