In SQL, joins are used to combine rows from two or more tables based on a related condition between them. I will show you how to use the most important joins: INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN, with NULL checks to handle unmatched rows.
1. INNER JOIN
INNER JOIN returns only the rows that have a match in both tables. No NULL check is needed, as it only returns rows that match in both tables.
Syntax:
SELECT columns FROM table1 INNER JOIN table2 ON table1.column = table2.column;
2. LEFT JOIN
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;
NULL Check:
SELECT customers.name, orders.product FROM customers LEFT JOIN orders ON customers.id = orders.customer_id WHERE orders.customer_id IS NULL;
3. RIGHT JOIN
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;
NULL Check:
SELECT customers.name, orders.product FROM customers RIGHT JOIN orders ON customers.id = orders.customer_id WHERE customers.id IS NULL;
4. FULL JOIN
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 does not have matches.
Syntax:
SELECT columns FROM table1 FULL JOIN table2 ON table1.column = table2.column;
NULL Check:
SELECT customers.name, orders.product FROM customers FULL JOIN orders ON customers.id = orders.customer_id WHERE customers.id IS NULL OR orders.customer_id IS NULL;
