What is SQL Joins?
An SQL JOIN clause combines rows from two or more tables. It creates a set of rows in a temporary table.
Pictorial Presentation of SQL Joins:
How to Join two tables in SQL?
A JOIN works on two or more tables if they have at least one common field and have a relationship between them.
JOIN keeps the base tables (structure and data) unchanged.
Join vs. Subquery
- JOINs are faster than a subquery and it is very rare that the opposite.
- In JOINs the RDBMS calculates an execution plan, that can predict, what data should be loaded and how much it will take to processed and as a result this process save some times, unlike the subquery there is no pre-process calculation and run all the queries and load all their data to do the processing.
- A JOIN is checked conditions first and then put it into table and displays; where as a subquery take separate temp table internally and checking condition.
- When joins are using, there should be connection between two or more than two tables and each table has a relation with other while subquery means query inside another query, has no need to relation, it works on columns and conditions.
SQL JOINS: EQUI JOIN and NON EQUI JOIN
The are two types of SQL JOINS - EQUI JOIN and NON EQUI JOIN
1) SQL EQUI JOIN :
The SQL EQUI JOIN is a simple SQL join uses the equal sign(=) as the comparison operator for the condition. It has two types - SQL Outer join and SQL Inner join.
2) SQL NON EQUI JOIN :
The SQL NON EQUI JOIN is a join uses comparison operator other than the equal sign like >, <, >=, <= with the condition.
SQL EQUI JOIN : INNER JOIN and OUTER JOIN
The SQL EQUI JOIN can be classified into two types - INNER JOIN and OUTER JOIN
1. SQL INNER JOIN
This type of EQUI JOIN returns all rows from tables where the key record of one table is equal to the key records of another table.
2. SQL OUTER JOIN
This type of EQUI JOIN returns all rows from one table and only those rows from the secondary table where the joined condition is satisfying i.e. the columns are equal in both tables.
In order to perform a JOIN query, the required information we need are:
a) The name of the tables
b) Name of the columns of two or more tables, based on which a condition will perform.
FROM table1 join_type table2 [ON (join_condition)]
|table1, table2||Tables participating in joining.|
|join_type||Type of the join.|
|join_condition||Some condition. This is optional.|
Sample table: company
Sample table: foods
To join two tables 'company' and 'foods', the following SQL statement can be used :
SELECT company.company_id,company.company_name, foods.item_id,foods.item_name FROM company,foods;
COMPAN COMPANY_NAME ITEM_ID ITEM_NAME ------ ------------------------- -------- --------------- 18 Order All 1 Chex Mix 18 Order All 6 Cheez-It 18 Order All 2 BN Biscuit 18 Order All 3 Mighty Munch 18 Order All 4 Pot Rice 18 Order All 5 Jaffa Cakes 18 Order All 7 Salt n Shake 15 Jack Hill Ltd 1 Chex Mix 15 Jack Hill Ltd 6 Cheez-It 15 Jack Hill Ltd 2 BN Biscuit 15 Jack Hill Ltd 3 Mighty Munch 15 Jack Hill Ltd 4 Pot Rice 15 Jack Hill Ltd 5 Jaffa Cakes 15 Jack Hill Ltd 7 Salt n Shake 16 Akas Foods 1 Chex Mix 16 Akas Foods 6 Cheez-It 16 Akas Foods 2 BN Biscuit 16 Akas Foods 3 Mighty Munch 16 Akas Foods 4 Pot Rice 16 Akas Foods 5 Jaffa Cakes 16 Akas Foods 7 Salt n Shake ......... ......... .........
JOINS: Relational Databases
Key points to remember:
Click on the following to get the slides presentation -
Practice SQL Exercises
- SQL Exercises, Practice, Solution
- SQL Retrieve data from tables [33 Exercises]
- SQL Boolean and Relational operators [12 Exercises]
- SQL Wildcard and Special operators [22 Exercises]
- SQL Aggregate Functions [25 Exercises]
- SQL Formatting query output [10 Exercises]
- SQL Quering on Multiple Tables [8 Exercises]
- FILTERING and SORTING on HR Database [38 Exercises]
- SQL JOINS
- SQL SUBQUERIES
- SQL Union[9 Exercises]
- SQL View[16 Exercises]
- SQL User Account Management [16 Exercise]
- Movie Database
- BASIC queries on movie Database [10 Exercises]
- SUBQUERIES on movie Database [16 Exercises]
- JOINS on movie Database [24 Exercises]
- Soccer Database
- BASIC queries on soccer Database [29 Exercises]
- SUBQUERIES on soccer Database [33 Exercises]
- Hospital Database
- Employee Database
- More to come!
Want to improve the above article? Contribute your Notes/Comments/Examples through Disqus.
SQL: Tips of the Day
Count the occurrences of DISTINCT values?
example db id name ----- ------ 1 Mark 2 Mike 3 Paul 4 Mike 5 Mike 6 John 7 Mark
SELECT name,COUNT(*) as count FROM tablename GROUP BY name ORDER BY count DESC;
name count ----- ----- Mike 3 Mark 2 Paul 1 John 1
Ref : https://bit.ly/3EXu62o
- Exercises: Weekly Top 16 Most Popular Topics
- SQL Exercises, Practice, Solution - JOINS
- SQL Exercises, Practice, Solution - SUBQUERIES
- Java Array: Exercises, Practice, Solution
- C Programming Exercises, Practice, Solution : Conditional Statement
- HR Database - SORT FILTER: Exercises, Practice, Solution
- C Programming Exercises, Practice, Solution : String
- Python Data Types: Dictionary - Exercises, Practice, Solution
- Python Programming Puzzles - Exercises, Practice, Solution
- C++ Array: Exercises, Practice, Solution
- C# Sharp Basic Algorithm: Exercises, Practice, Solution
- Python Lambda - Exercises, Practice, Solution
- Python Pandas DataFrame: Exercises, Practice, Solution
- Conversion Tools