The SQL JOIN joins two tables based on a common column, and selects records that have matching values in these columns.
Example
SELECT Customers.customer_id, Customers.first_name, Orders.amount
FROM Customers
JOIN Orders
ON Customers.customer_id = Orders.customer;
Here's how this code works:
Here, the SQL command selects customer_id and first_name columns (from the Customers table) and the amount column (from the Orders table).
And, the result set will contain those rows where there is a match between customer_id (of the Customers table) and customer (of the Orders table).
Types of SQL JOINs
The JOIN command we performed earlier is INNER JOIN. There are mainly four types of joins.
- INNER JOIN (Same as
JOIN) - LEFT JOIN
- RIGHT JOIN
- FULL OUTER JOIN
SQL JOIN and Aliases
We can use AS aliases with table names to make our snippet short and clean. For example,
SELECT C.customer_id, C.first_name, O.amount
FROM Customers AS C
JOIN Orders AS O
ON C.customer_id = O.customer_id;
Also, we can change the column names temporarily using AS aliases. For example,
SELECT C.customer_id AS cid, C.first_name AS name, O.amount
FROM Customers AS C
JOIN Orders AS O
ON C.customer_id = O.customer_id;
Here, these snippets would work exactly the same as earlier.