In SQL, a JOIN operation is used to combine data from two or more tables based on a related column between them. There are several types of JOINs, including INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN. In this tutorial, we will focus on the INNER JOIN, which is the most commonly used type of JOIN.
SQL INNER JOIN:
An INNER JOIN returns only the rows that have matching values in both tables. The matching condition is specified in the ON clause of the SQL statement. The basic syntax of an INNER JOIN is as follows:
SELECT table1.column1, table1.column2, table2.column1, table2.column2 FROM table1 INNER JOIN table2 ON table1.column1 = table2.column1;
In this example, we are selecting columns from two tables,
table2, and joining them on a common column
column1. The result will contain only the rows from both tables where the value of
Let’s consider an example to understand it better.
Suppose we have two tables
orders with the following data:
If we want to join the two tables on the
customer_id column, we can use the following SQL statement:
SELECT customers.name, orders.order_date FROM customers INNER JOIN orders ON customers.customer_id = orders.customer_id;
The result of this query will be:
Note that we have specified the column names using the table aliases
ON clause specifies the condition for the JOIN operation, which in this case is the equality of
customer_id columns from both tables.
Advanced concepts related to SQL INNER JOIN
JOIN with Multiple Tables
In SQL, you can join multiple tables using INNER JOIN. For example, if you have three tables, named Table1, Table2, and Table3, and you want to join them based on a common column, you can use the following syntax:
SELECT * FROM Table1 INNER JOIN Table2 ON Table1.common_column = Table2.common_column INNER JOIN Table3 ON Table2.common_column = Table3.common_column;
This will join all three tables based on the common column, and you can select all columns from all tables using
A self-join is a join in which a table is joined with itself. This is useful when you want to compare rows within the same table. For example, if you have a table named Employee with columns EmployeeID and ManagerID, you can use the following syntax to find all employees and their managers:
SELECT e.EmployeeID, e.Name, m.Name AS ManagerName FROM Employee e INNER JOIN Employee m ON e.ManagerID = m.EmployeeID;
In this example, the same table is used twice, with different aliases (
m) for each instance.
JOIN with Subqueries
You can also use subqueries to join tables in SQL. This is useful when you want to filter the data in one table based on the data in another table. For example, if you have two tables named Orders and Customers, and you want to find all orders for customers in a specific city, you can use the following syntax:
SELECT * FROM Orders INNER JOIN( SELECT CustomerID FROM Customers WHERE City = 'New York' ) AS NewYorkCustomers ON Orders.CustomerID = NewYorkCustomers.CustomerID;
In this example, the subquery selects all customer IDs from the Customers table that have a City value of ‘New York’, and this is used to filter the Orders table using INNER JOIN.
The INNER JOIN operation is used to combine data from two or more tables based on a related column between them. It returns only the rows that have matching values in both tables. In this tutorial, we have explained the syntax of the INNER JOIN statement and provided an example to demonstrate its usage.