INNER JOIN joins two tables based on a common column and selects rows that have matching values in these columns.
-- join Customers and Orders tables with their matching fields customer_id SELECT Customers.customer_id, Orders.item FROM Customers INNER JOIN Orders ON Customers.customer_id = Orders.customer;
Here, the SQL query performs an INNER JOIN operation by joining the
Orders tables. It then filters the customer_id column of the Customers table and the
item column of the Orders table into the result set.
SQL INNER JOIN Syntax
The syntax of the SQL
INNER JOIN statement is:
SELECT columns_from_both_tables FROM table1 INNER JOIN table2 ON table1.column1 = table2.column2
- 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
INNER JOIN excludes all the rows that are not common between two tables.
Note: We can also use
JOIN instead of
INNER JOIN. Basically, these two clauses are the same.
Example 1: SQL INNER JOIN
-- join the Customers and Orders tables with matching fields customer_id and customer SELECT Customers.customer_id, Customers.first_name, Orders.amount FROM Customers INNER JOIN Orders ON Customers.customer_id = Orders.customer;
Here, the SQL command selects rows from both tables if the values of customer_id (of the Customers table) and customer (of the Orders table) are a match.
Example 2: SQL INNER JOIN
Let's look at another example,
-- join Categories and Products tables with their matching fields cat_id SELECT Categories.cat_name, Products.prod_title FROM Categories INNER JOIN Products ON Categories.cat_id = Products.cat_id;
Here, the SQL command selects common rows between Categories and Products tables with the matching field cat_id.
The result set has the
cat_name column from
Categories and the
prod_title column from
INNER JOIN With WHERE Clause
Here's an example of
INNER JOIN with the
-- join Customers and Orders table with matching fields customer_id and customer SELECT Customers.customer_id, Customers.first_name, Orders.amount FROM Customers INNER 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.
SQL INNER JOIN With AS Alias
We can use
AS aliases inside
INNER JOIN to make our query short and clean. For example,
-- use alias C for Categories table -- use alias P for Products table SELECT C.cat_name, P.prod_title FROM Categories AS C INNER JOIN Products AS P ON C.cat_id= P.cat_id;
Here, the SQL command performs an inner join on the Categories and Products tables while assigning the aliases C and P to them, respectively.
SQL INNER JOIN With Three Tables
We can also join more than two tables using
INNER JOIN. For example,
-- join three tables: Customers, Orders, and Shippings SELECT C.customer_id, C.first_name, O.amount, S.status FROM Customers AS C INNER JOIN Orders AS O ON C.customer_id = O.customer INNER JOIN Shippings AS S ON C.customer_id = S.customer;
Here, the SQL command
Orderstables based on
- and joins
Shippingsstables based on
The command returns those rows where there is a match between column values in both join conditions.
Note: For this command to run, there must be a
customer_id column in each individual table. The column names can be different as long as they have common data.