SQL JOIN clauses are used to combine columns from two or more tables based on a related column between them. Because relational databases isolate data into separate normalized tables to eliminate redundancy, joins are the mechanism used to stitch that data back together into a single, cohesive result set
- For the purposes of practice we'll use these two tables; customers and items
SQL Join Types
Inner join
An INNER JOIN in SQL combines rows from two or more tables based on a related column between them. It returns only the records that have matching values in both tables; if a row in one table does not have a matching row in the other, it is excluded from the final result
SELECT orders.order_id, customers.customer_name
FROM orders
INNER JOIN customers
ON orders.customer_id = customers.customer_id;
Left Join
In SQL, a LEFT JOIN (also known as a LEFT OUTER JOIN) returns all rows from the left table (the first table mentioned), along with the matching rows from the right table. If there is no match for a row from the left table, the columns from the right table will simply display NULL
SELECT o.order_id, c.customer_name
FROM orders AS o
LEFT JOIN customers AS c
ON o.customer_id = c.customer_id;
Right Join
The SQL RIGHT JOIN (or RIGHT OUTER JOIN) returns all records from the right table (table2) and the matched records from the left table (table1). If there is no match for a row from the right table, the resulting columns from the left table will contain NULL values.
SELECT table1.column_name, table2.column_name
FROM table1
RIGHT JOIN table2
ON table1.common_column = table2.common_column;
Full Join
A SQL FULL JOIN (also known as a FULL OUTER JOIN) returns all records from both tables whenever there is a match in either the left or right table. If a row from the left table matches a row from the right table, the columns are combined. If there is no match, the missing sides are populated with NULL values. Essentially, a FULL JOIN acts as a combination of a LEFT JOIN and a RIGHT JOIN
SELECT table1.column_name, table2.column_name
FROM table1
FULL OUTER JOIN table2
ON table1.common_column = table2.common_column;
Top comments (1)
Nice article keep up