When working with SQL databases, we may need to use different methods to retrieve data from one or more tables. SQL JOIN and Subquery are used to retrieve data from multiple tables and combine them into a single result set. The key difference between SQL join and subquery is that join will combine records of two or more tables, whereas subquery is a query nested inside another query. In this blog, we will learn the difference between JOIN and Subquery.
In this blog, let us explore what JOIN and Subquery are in SQL, along with the differences between them in detail, with examples for each.
Table of Contents:
Before we explore the JOIN and Subquery in SQL, let’s first create a dataset to understand them better. We will use this dataset for all the examples to help you understand them easily.
-- Create an author's table
CREATE TABLE authors (
author_id INT IDENTITY(1,1) PRIMARY KEY,
name NVARCHAR(100) NOT NULL,
country NVARCHAR(50)
);
-- Create a book table
CREATE TABLE books (
book_id INT IDENTITY(1,1) PRIMARY KEY,
title NVARCHAR(200) NOT NULL,
author_id INT,
price DECIMAL(10,2),
FOREIGN KEY (author_id) REFERENCES authors(author_id)
);
-- Insert values into the author's table
INSERT INTO authors (name, country) VALUES
('George Orwell', 'United Kingdom'),
('J.K. Rowling', 'United Kingdom'),
('Harper Lee', 'United States');
-- Insert values into the book table
INSERT INTO books (title, author_id, price) VALUES
('1984', 1, 15.99),
('Animal Farm', 1, 12.50),
('Harry Potter and the Sorcerer''s Stone', 2, 25.99),
('To Kill a Mockingbird', 3, 18.75);
-- Retrieve and display books with optimized output
SELECT
LEFT(title, 15) AS Book_Title,
author_id,
CAST(price AS DECIMAL(6,2)) AS Price
FROM books;Output:
This is how the table with the author ID and their books table will look after creating it.
What is a JOIN in SQL?
A JOIN in SQL is used to combine two or more tables based on conditions or matching records between them. There are different types of JOINs in SQL.
Types of JOINs in SQL:
- INNER JOIN
- OUTER JOIN
- LEFT OUTER JOIN
- RIGHT OUTER JOIN
- FULL OUTER JOIN
INNER JOIN in SQL
The INNER JOIN is a type of JOIN in SQL where it fetches only the matched rows between the tables. It won’t fetch the rows if there are no matching values in the table.
Example:
-- INNER JOIN (Only Matching Records)
SELECT
LEFT(b.title, 15) AS Book_Title,
LEFT(a.name, 12) AS Author,
b.price AS Price
FROM books b
INNER JOIN authors a ON b.author_id = a.author_id;Output:
Explanation: The INNER JOIN query retrieves book titles with their author’s name from both tables.
LEFT JOIN in SQL
The LEFT JOIN in SQL retrieves all records from the left table and the matching records from the right table. If no match is found, NULL values are returned from the right table.
Example:
-- Create an author's table
CREATE TABLE authors (
author_id INT IDENTITY(1,1) PRIMARY KEY,
name NVARCHAR(100) NOT NULL,
country NVARCHAR(50)
);
-- Create books table (Allow NULL for author_id)
CREATE TABLE books (
book_id INT IDENTITY(1,1) PRIMARY KEY,
title NVARCHAR(200) NOT NULL,
author_id INT NULL, -- Allow NULL to demonstrate missing authors
price DECIMAL(10,2),
FOREIGN KEY (author_id) REFERENCES authors(author_id) ON DELETE SET NULL
);
-- Insert values into the authors' table
INSERT INTO authors (name, country) VALUES
('George Orwell', 'United Kingdom'),
('J.K. Rowling', 'United Kingdom'),
('Harper Lee', 'United States'),
('Shakespeare', 'Unknown Country'); -- This author has no books
-- Insert values into the book table
INSERT INTO books (title, author_id, price) VALUES
('1984', 1, 15.99),
('Animal Farm', 1, 12.50),
('Harry Potter and the Sorcerer''s Stone', 2, 25.99),
('To Kill a Mockingbird', 3, 18.75);
-- LEFT JOIN Query (Shows all authors, even if they have no books)
SELECT
LEFT(ISNULL(b.title, 'No Book'), 15) AS Book_Title,
LEFT(a.name, 12) AS Author,
ISNULL(b.price, 0.00) AS Price
FROM authors a
LEFT JOIN books b ON a.author_id = b.author_id;Output:
Explanation: The left join retrieves all the records from the left table (authors) and the matching records from the right table (books), if there is no match, then it will return as no book.
RIGHT JOIN in SQL
The right join in SQL retrieves all the data from the right table and the matching data from the left table. If no match is found, NULL values are returned from the left table.
Example:
-- Create an author's table
CREATE TABLE authors (
author_id INT IDENTITY(1,1) PRIMARY KEY,
name NVARCHAR(100) NOT NULL,
country NVARCHAR(50)
);
-- Create books table (Allow NULL for author_id)
CREATE TABLE books (
book_id INT IDENTITY(1,1) PRIMARY KEY,
title NVARCHAR(200) NOT NULL,
author_id INT NULL, -- Allow NULL to store books without authors
price DECIMAL(10,2),
FOREIGN KEY (author_id) REFERENCES authors(author_id) ON DELETE SET NULL
);
-- Insert values into the authors' table
INSERT INTO authors (name, country) VALUES
('George Orwell', 'United Kingdom'),
('J.K. Rowling', 'United Kingdom'),
('Harper Lee', 'United States');
-- Insert values into the books table (With valid author_id)
INSERT INTO books (title, author_id, price) VALUES
('1984', 1, 15.99),
('Animal Farm', 1, 12.50),
('Harry Potter and the Sorcerer''s Stone', 2, 25.99),
('To Kill a Mockingbird', 3, 18.75);
-- Insert a book with NULL author_id (To allow books without authors)
INSERT INTO books (title, author_id, price) VALUES
('Wings of fire', NULL, 10.00); -- Now allowed due to NULL author_id
-- RIGHT JOIN Query (Shows all books, even if they have no authors)
SELECT
LEFT(ISNULL(b.title, 'No Book'), 15) AS Book_Title,
LEFT(ISNULL(a.name, 'No Author'), 12) AS Author,
ISNULL(b.price, 0.00) AS Price
FROM authors a
RIGHT JOIN books b ON a.author_id = b.author_id;Output:
Explanation: Returns all the rows from the right table (books) and fetches matching rows from the left table (authors). If there are no author details, the table will show No author.
FULL JOIN in SQL
The full join in SQL retrieves all the data from both tables, including the null values.
Example:
-- Create an author's table
CREATE TABLE authors (
author_id INT IDENTITY(1,1) PRIMARY KEY,
name NVARCHAR(100) NOT NULL,
country NVARCHAR(50)
);
-- Create books table (Allow NULL for author_id)
CREATE TABLE books (
book_id INT IDENTITY(1,1) PRIMARY KEY,
title NVARCHAR(200) NOT NULL,
author_id INT NULL, -- Allow NULL to store books without authors
price DECIMAL(10,2),
FOREIGN KEY (author_id) REFERENCES authors(author_id) ON DELETE SET NULL
);
-- Insert values into the authors' table
INSERT INTO authors (name, country) VALUES
('George Orwell', 'United Kingdom'),
('J.K. Rowling', 'United Kingdom'),
('Harper Lee', 'United States');
INSERT INTO authors (name, country) VALUES
('Shakespeare', 'Unknown Country');
-- Insert values into the books table (With valid author_id)
INSERT INTO books (title, author_id, price) VALUES
('1984', 1, 15.99),
('Animal Farm', 1, 12.50),
('Harry Potter and the Sorcerer''s Stone', 2, 25.99),
('To Kill a Mockingbird', 3, 18.75);
-- Insert a book with NULL author_id (To allow books without authors)
INSERT INTO books (title, author_id, price) VALUES
('Wings of fire', NULL, 10.00); -- Now allowed due to NULL author_id
-- FULL OUTER JOIN (All Books & Authors, Even Without Matches)
SELECT
LEFT(ISNULL(b.title, 'No Book'), 15) AS Book_Title,
LEFT(ISNULL(a.name, 'No Author'), 12) AS Author,
ISNULL(b.price, 0.00) AS Price
FROM books b
FULL OUTER JOIN authors a ON b.author_id = a.author_id;Output:
Explanation: The full join retrieves all the data from both tables, which are the book title and price table, including null values in them.
What is a Subquery in SQL?
A subquery is a query that is inside another nested SQL query. The inner query will always execute first, then the outer query.
Example:
SELECT
LEFT(title, 20) AS title,
CAST(price AS DECIMAL(6,2)) AS price
FROM books
WHERE author_id IN (SELECT author_id FROM authors WHERE country = 'United Kingdom');Output:
Explanation: The subquery fetched the record of books that were sold in the United Kingdom.
Types of Subqueries in SQL
- Scalar Subquery in SQL
- Single-row subquery in SQL
- Multi-row subquery in SQL
- Correlated subquery in SQL
1. Scalar Subquery in SQL
A scalar subquery is a type of subquery in SQL that returns only one single value. It uses SELECT, WHERE, and HAVING clauses while performing operations.
Example:
SELECT
LEFT(title, 20) AS title,
CAST(price AS DECIMAL(6,2)) AS price
FROM books
WHERE price = (SELECT MAX(price) FROM books);Output:
Explanation: The scalar subquery used WHERE and SELECT queries to fetch the most expensive books from the table.
2. Single-Row Subquery in SQL
The single-row subquery in SQL fetched only one row with one or more columns. It uses operators like =, >,<, >=, and <=.
Example:
SELECT
LEFT(name, 15) AS author_name
FROM authors
WHERE author_id = (SELECT author_id FROM books WHERE price = (SELECT MIN(price) FROM books));Output:
Explanation: The Single-row operator used the “=” operator to fetch the author name of the cheapest book.
3. Multi-Row Subquery in SQL
The multi-row subquery in SQL will return multiple rows but only one column, it uses operators like IN, ANY, ALL, or EXISTS.
Example:
SELECT
LEFT(title, 20) AS title,
CAST(price AS DECIMAL(6,2)) AS price
FROM books
WHERE author_id IN (SELECT author_id FROM authors WHERE country = 'United Kingdom');Output:
Explanation: The multi-row subquery uses the “IN” operator to fetch the books written by authors who reside in the United Kingdom.
4. Correlated Subquery in SQL
A Correlated subquery in SQL will return the query that depends on the outer query for each row it processes and gives the output.
Example:
SELECT
LEFT(title, 20) AS title,
CAST(price AS DECIMAL(6,2)) AS price
FROM books b1
WHERE price > (SELECT AVG(price) FROM books b2 WHERE b1.author_id = b2.author_id);Output:
Explanation: The correlated subquery fetched a higher price compared to the average price of all books written by the same author.
Differences Between JOIN and Subquery in SQL
| Feature | JOIN | Subquery |
| Performance | Join works faster when it works with large datasets. | Subquery is slower in performance as it needs to be optimized properly. |
| Readability | In join, sometimes queries will be more complex and at that time readability is hard. | Subquery is easier to read because it simplifies complex queries by breaking them into smaller parts. |
| Execution | Join will execute faster in a single execution. Executes in a single pass | A subquery is an inner query so the inner query has to execute first, then only the outer query, so the execution time will be slow. |
| Use Case | Join is used to combine one or more tables in a single execution. | Subquery is used to filter data or any operations that have to be done in an inner query. |
Performance Considerations
- Joins are more efficient than subqueries. Because join makes use of indexes and optimized join engines to get efficient output.
- Subqueries are less efficient than joins because subqueries involve correlated subqueries that may need to be executed multiple times.
Best Practices
- Use joins for most of the time, as they are more efficient and easier to execute large datasets.
- If you ensure proper indexing, the execution of joins for filtering columns will be easier.
- Subqueries are used to filter the values before filtering with the help of aggregate functions.
- We can avoid correlated subqueries when not needed because correlated will execute only once per row, so it will delay the performance execution time.
When to Use JOIN vs. Subquery
JOINS
- To retrieve data from multiple tables.
- To optimize large datasets.
- Joins have various types of joins like inner, outer, full, right, and left joins for different types of performance.
SUBQUERY
- To write simple and readable queries.
- To filter data before combining them from multiple tables.
- To perform operations with multiple aggregate functions for an efficient result.
Conclusion
Joins are faster in performance and will be efficient in optimizing large datasets, whereas Subqueries are more readable and easier to perform queries and filter data. Joins are used to combine tables, and subqueries are used for nested queries. For performance and scalability, use JOINs. Whereas for filtering or aggregation, you can use Subquery.
To learn more about SQL functions, check out this SQL course and also explore SQL Interview Questions prepared by industry experts.
SQL Join vs Subquery – FAQs
1. Which is better, subquery or join in SQL?
JOINs are generally better for performance, while subqueries improve readability in specific cases.
2. Can we replace subquery with join?
Yes, in most cases, subqueries can be rewritten using JOINs for better performance.
3. Which join is faster in SQL?
INNER JOIN is usually the fastest because it only retrieves matching records, reducing data processing.
4. Do subqueries slow down performance?
Yes, subqueries can be slower, especially correlated subqueries, as they execute multiple times.
5. Are join queries slow?
JOINs can be slow if tables are large and not properly indexed, but they are generally more efficient than subqueries.