Understanding the Concept of JOIN in SQL Database

JOIN in SQL is a clause used to combine rows from two or more tables based on a related column between them. Its main purpose is to obtain a complete and comprehensive set of data. "Complete" here refers to the data arrangement obtained from the combination of columns resulting from the relationship between those tables.

To meet various data retrieval needs, the JOIN command is divided into several types: OUTER JOIN, INNER JOIN, RIGHT JOIN, and LEFT JOIN.

Differences Between Types of SQL JOIN

Here is an explanation of the various types of JOIN clauses along with their syntax examples to help you understand the differences:

1. FULL OUTER JOIN

Outer join (or Full Outer Join) will return all records or rows, whether they have matching values or not, from both tables being joined. If there is no data match between the two tables, the empty parts will automatically be given NULL values in the join result.

Syntax:

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

2. LEFT JOIN

Left Join is an operation that returns all records from the left table (the first table) of the two tables being joined. All data from the left table will still be retrieved and displayed, even if the values in the left table have no match with data in the right table (the second table). Data in the right table that has no match will be replaced with NULL.

Syntax:

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

3. RIGHT JOIN (Supplementary)

As the opposite of Left Join, the Right Join clause will return all records from the right table (the second table), along with matching data from the left table. If there is no matching data in the left table, the result from the left side will be NULL.

Syntax:

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

4. INNER JOIN (Supplementary)

Inner Join is the most commonly used type of clause. Unlike Outer, Inner Join works more strictly by only returning rows whose data has a match (intersection) in both tables being joined.

Syntax:

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