SQL JOIN allows you to combine records from two or more tables into a temporary table called join-table. SQL provides five types of join: inner join, outer join, right join, left join and self-join.
Sample tables
We have two tables: categories and products in the sample database to demonstrate how the SQL JOIN works. Here is the database diagram of them:
- One categories can have many products
- One product has only one category.
SQL INNER JOIN
The syntax of SQL INNER JOIN is as follows:
SELECT selection_list
FROM table_A
INNER JOIN table_B ON join_condition
WHERE row_conditions.
Table_A and table_B are sometimes called joined-tables. SQL INNER JOIN returns records from both tables where a match is found based on join conditions (join_condition). SQL INNER JOIN gets all the records from the table_A and finds the matching records in the table_B according to the join condition. The join condition determines whether both records are matched or not. If there is no match found, no records will be returned.
Suppose you need the following information: productId, productName, and category name of each product from both tables products and categories. In this case, you can use SQL INNER JOIN to combine the records in both tables. You can perform the following query to do so:
SELECT productID,productName,categoryName
FROM products
INNER JOIN categories ON products.categoryID = categories.categoryID
Here is the excerpt result from the query:
productID productName categoryName
--------- --------------------------------- --------------
1 Chai Beverages
2 Chang Beverages
3 Aniseed Syrup Condiments
4 Chef Anton's Cajun Seasoning Condiments
5 Chef Anton's Gumbo Mix Condiments
6 Grandma's Boysenberry Spread Condiments
7 Uncle Bob's Organic Dried Pears Produce
8 Northwoods Cranberry Sauce Condiments
9 Mishi Kobe Niku Meat/Poultry
10 Ikura Seafood
For each product in the products table, SQL finds category in the categories table which has the same categoryID; If there is a match found, SQL returns records otherwise no record is returned. Most of the time you use foreign keys to form the join condition. In this case categoryID is the foreign key of two tables.
There is another form of SQL INNER JOIN which called implicit inner join. Here is the implicit SQL INNER JOIN syntax:
SELECT selection_list
FROM table_A, table_B
WHERE join_condition.
In this form you list all joined-tables after the FROM clause and put join condition in WHERE clause of the SQL SELECT statement. As our above example, we can rewrite the example query as follows:
SELECT productID,productName,categoryName
FROM products, categories
WHERE products.categoryID = categories.categoryID
There is another way to visualize the SQL INNER JOIN by using the Venn diagrams. In this way you can see that SQL INNER JOIN returns only records that match in both table_A and table_ B.
.jpg)
In this tutorial, you've learned how to use SQL INNER JOIN to retrieve data from two tables. In the next tutorial, you will learn how to use another kind of SQL join called outer join.