SQL JOINS

-- join the Customers and Orders tables
-- based on the common values of their customer_id columns

SELECT Customers.customer_id, Customers.first_name, Orders.item
FROM Customers
JOIN Orders
ON Customers.customer_id = Orders.customer_id;

SQL JOIN Syntax

The syntax of the SQL JOIN statement is:

SELECT columns_from_both_tables
FROM table1
JOIN table2
ON table1.column1 = table2.column2

Here,

• table1 and table2 are the two tables that are to be joined
• column1 is the column in table1 that is related to column2 in table2

Note: There are 4 types of JOINs in SQL. But INNER JOIN and JOIN refer to the same thing.

Example 1: SQL JOIN

-- join Customers and Orders tables based on
-- customer_id of Customers and customer column of Orders

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 the customer_id and first_name columns (from the Customers table) and the amount column (from the Orders table).

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

As we mentioned, the JOIN command we performed in this article is INNER JOIN.

In SQL, we have four main types of joins:

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL OUTER JOIN

SQL JOIN and Aliases

We can use AS aliases with table names to make our query short and clean. For example,

-- use alias C for Customers table
-- use alias O for Orders table
SELECT C.customer_id, C.first_name, O.amount
FROM Customers AS C
JOIN Orders AS O
ON C.customer_id = O.customer;

Here, the SQL command joins the Customers and Orders tables while assigning the aliases C and O to them, respectively.

Also, we can change the column names temporarily using AS aliases. For example,

-- use alias C for Customers table
-- use alias O for Orders table
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;

Apart from giving aliases to the tables, the SQL command above also assigns aliases to the columns of the Customers table:

• customer_id column has the alias cid
• first_name column has the alias name