Learn about SQL JOINS with the help of examples. Learn how to combine data from two or more tables in your SQL queries with the SQL JOIN operator.
The SQL JOIN
joins two tables based on a common column and selects records that have matching values in these columns.
-- 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;
Here, the SQL command joins the Customers
and Orders
tables based on the common values in the customer_id
columns of both tables.
The result set will consist of
customer_id
and first_name
columns from the Customers
tableitem
column from the Orders
tableThe syntax of the SQL JOIN
statement is:
SELECT columns_from_both_tables
FROM table1
JOIN table2
ON table1.column1 = table2.column2
Here,
Note: There are 4 types of JOINs in SQL. But INNER JOIN
and JOIN
refer to the same thing.
-- 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:
Example: SQL JOIN
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).
As we mentioned, the JOIN
command we performed in this article is INNER JOIN
.
In SQL, we have four main types of joins:
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
#sql