INNER JOIN command joins two tables based on a common column and selects rows with matching values in those columns.
-- join Customers and Orders tables -- select customer_id and first_name columns from the Customers table -- select amount from the Orders table SELECT Customers.customer_id, Customers.first_name, Orders.amount FROM Customers INNER JOIN Orders ON Customers.customer_id = Orders.customer;
Here, the Customers and Orders table are joined on the
Customers.customer_id = Orders.customer condition. The above code excludes all the rows that don't satisfy this condition.
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 a column in table1 and column2 in a column in table2
Note: We can use
JOIN instead of
INNER JOIN. Basically, these two clauses perform the same task.
Example 1: SQL INNER JOIN
-- join the Customers and Orders tables when -- the customer_id from Customers matches the customer column in Orders SELECT Customers.customer_id, Customers.first_name, Orders.amount FROM Customers JOIN Orders ON Customers.customer_id = Orders.customer;
Here is how the above SQL query works:
Example 2: SQL INNER JOIN
Let's look at another example.
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 table based on the cat_id column, which is present in both tables.
INNER JOIN With WHERE Clause
We can use the
WHERE clause in conjunction with an
INNER JOIN to further filter the rows in the output.
-- join tables the Customers and Orders tables -- return rows if amount is 500 or higher SELECT Customers.customer_id, Customers.first_name, Orders.amount FROM Customers INNER JOIN Orders ON Customers.customer_id = Orders.customer WHERE Orders.amount >= 500;
SQL INNER JOIN With AS Alias
Since join queries can be long, we can use the AS alias within
INNER JOIN to make our queries more concise and easier to understand.
-- 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 across both join conditions.