SQL JOINS

The SQL JOIN statement is used to combine rows from two tables based on a common column and selects records that have matching values in these columns.

Example

-- 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 column, customer_id of both tables.

The result set will consist of

  • customer_id and first_name columns from the Customers table
  • item column from the Orders table

SQL JOIN Syntax

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

Example: Join Two Table Based on Common Column

-- 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, the SQL command selects the columns:

  • customer_id and first_name from the Customers table
  • amount from the Orders table

The result set will contain those values where there is a match between customer_id (of the Customers table) and customer (of the Orders table).

Join Two Table Based on Common Column
Join Two Table Based on Common Column


JOIN Multiple Tables

We can also join more than two tables using JOIN. For example,

-- join three tables: Customers, Orders, and Shippings

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

This SQL command joins three tables and selects relevant columns from each, based on the matching customer_id.

Note: To learn more about how to join multiple tables, visit SQL Join Multiple Tables.


Types of SQL JOINs

In SQL, we have four main types of joins:


More on SQL JOIN

SQL Self JOIN

In SQL, the Self JOIN operation allows us to join a table with itself, creating a relationship between rows within the same table.

Let's look at an example.

SELECT
    C1.first_name AS FirstPerson,
    C2.first_name AS SecondPerson,
    C1.country
FROM Customers C1, Customers C2
WHERE C1.country = C2.country AND C1.first_name != C2.first_name;

The SQL query will return pairs of customers who are from the same country but have different first names.

To learn more, Visit SQL Self JOIN.

SQL JOIN With AS Alias

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
JOIN With WHERE Clause

Here's an example of JOIN with the WHERE clause:

-- join Customers and Orders table with matching fields customer_id and customer

SELECT Customers.customer_id, Customers.first_name, Orders.amount
FROM Customers
JOIN Orders
ON Customers.customer_id = Orders.customer
WHERE Orders.amount >= 500;

Here, the SQL command joins two tables and selects rows where the amount is greater than or equal to 500.

Did you find this article helpful?

Our premium learning platform, created with over a decade of experience and thousands of feedbacks.

Learn and improve your coding skills like never before.

Try Programiz PRO
  • Interactive Courses
  • Certificates
  • AI Help
  • 2000+ Challenges