SQL join two tables related by a single column primary key or foreign key pair using where clause

Description

In this page, we are going to discuss the usage of two or more tables in a joining with single column PRIMARY KEY and FOREIGN KEY.

Example:

+---------+--------------+-----------+------------+
| ITEM_ID | ITEM_NAME    | ITEM_UNIT | COMPANY_ID |
+---------+--------------+-----------+------------+
| 1       | Chex Mix     | Pcs       | 16         |
| 6       | Cheez-It     | Pcs       | 15         |
| 2       | BN Biscuit   | Pcs       | 15         |
| 3       | Mighty Munch | Pcs       | 17         |
| 4       | Pot Rice     | Pcs       | 15         |
| 5       | Jaffa Cakes  | Pcs       | 18         |
| 7       | Salt n Shake | Pcs       |            |
+---------+--------------+-----------+------------+

+------------+---------------+--------------+
| COMPANY_ID | COMPANY_NAME  | COMPANY_CITY |
+------------+---------------+--------------+
| 18         | Order All     | Boston       |
| 15         | Jack Hill Ltd | London       |
| 16         | Akas Foods    | Delhi        |
| 17         | Foodies.      | London       |
| 19         | sip-n-Bite.   | New York     |
+------------+---------------+--------------+

To get 'item_name' and 'item_unit' from 'foods' table and 'company_name' and 'company_city' form 'company' table after a joining with following conditions-

1. 'company_id' is primary key in 'company' table,

2. 'company_id' is foreign key in 'foods' table which is referencing to the, primary key of 'company' table,

3. 'company_id' of 'company' and 'foods' must be same,

4. 'company_city' of 'company' column must be 'London',

the following SQL statement can be used :

SQL Code:

-- Selecting specific columns: 'item_name' and 'item_unit' from the 'foods' table, and 'company_name' and 'company_city' from the 'company' table
SELECT foods.item_name, foods.item_unit, company.company_name, company.company_city
-- Performing a Cartesian product (cross join) between the 'foods' and 'company' tables (implicit join)
FROM foods, company
-- Defining the join condition in the WHERE clause where 'foods.company_id' equals 'company.company_id'
WHERE foods.company_id = company.company_id
-- Filtering the result set to include only rows where 'company_city' is 'London'
AND company.company_city = 'London';

Explanation:

• This SQL query retrieves data from two tables: 'foods' and 'company'.


• It selects specific columns from these tables: 'item_name' and 'item_unit' from the 'foods' table, and 'company_name' and 'company_city' from the 'company' table.


• The query performs a Cartesian product (cross join) between the 'foods' and 'company' tables, implicitly joining every row from the 'foods' table with every row from the 'company' table.


• The join condition is specified in the WHERE clause, where 'foods.company_id' must equal 'company.company_id', linking the two tables based on the company ID.


• Additionally, the WHERE clause includes a condition to filter the result set, ensuring that only rows where 'company_city' is 'London' are included.


• This query is useful for retrieving information about food items and the companies located in London, providing insight into the types of food products available in that city and the companies producing them.

Output

ITEM_NAME                 ITEM_UNIT  COMPANY_NAME              COMPANY_CITY
------------------------- ---------- ------------------------- -------------
Cheez-It                  Pcs        Jack Hill Ltd             London
BN Biscuit                Pcs        Jack Hill Ltd             London
Mighty Munch              Pcs        Foodies.                  London
Pot Rice                  Pcs        Jack Hill Ltd             London

See our Model Database

Here is a new document which is a collection of questions with short and simple answers, useful for learning SQL as well as for interviews.

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.

Previous: Joining tables with group by and order by
Next: Join two tables related by a composite primary key or foriegn key pair