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:
Sample table: foods
Sample table: company
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
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
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics