w3resource

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


Introduction

This page explores the concept of joining two tables in SQL using a WHERE clause, focusing on the relationship between a single-column primary key and foreign key pair. Such joins are a fundamental aspect of relational databases, enabling the combination of data from multiple tables based on shared keys.

Using the foods and company tables as examples, this page demonstrates how to retrieve meaningful insights by joining tables where the company_id in the foods table acts as a foreign key referencing the primary key in the company table.

The page provides a clear explanation of the SQL syntax and logic used to achieve this, making it a valuable resource for understanding table joins in SQL.

Example:

Sample table: foods

+---------+--------------+-----------+------------+
| 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       |            |
+---------+--------------+-----------+------------+

Sample table: company

+------------+---------------+--------------+
| 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



Follow us on Facebook and Twitter for latest update.