When dealing with SQL databases, it may be necessary to employ various techniques to fetch data from one or more tables. SQL JOIN and Subquery serve to extract data from multiple tables and merge them into a unified result set. The primary distinction between an SQL JOIN and a subquery is that a JOIN merges records from two or more tables, while a subquery represents a query embedded within another query. In this article, we will examine the variance between JOIN and Subquery.

In this article, we will delve into what JOIN and Subquery are in SQL, alongside the specific differences between them, providing examples for clarity.

Table of Contents:

• What is a JOIN in SQL?
  • INNER JOIN in SQL
  • LEFT JOIN in SQL
  • RIGHT JOIN in SQL
  • FULL JOIN in SQL
• What is a Subquery in SQL?
• Types of Subqueries in SQL
  • Scalar Subquery in SQL
  • Single-Row Subquery in SQL
  • Multi-Row Subquery in SQL
  • Correlated Subquery in SQL
• Differences Between JOIN and Subquery in SQL
• Performance Considerations
• Best Practices
• When to Use JOIN vs. Subquery
• Conclusion

Before we investigate JOIN and Subquery in SQL, let’s create a dataset for clearer understanding. This dataset will be employed across all examples for better comprehension.

-- Create an authors table

CREATE TABLE authors (

    author_id INT IDENTITY(1,1) PRIMARY KEY,

    name NVARCHAR(100) NOT NULL,

    country NVARCHAR(50)

);

-- Create a books 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 entries into the authors table

INSERT INTO authors (name, country) VALUES

('George Orwell', 'United Kingdom'),

('J.K. Rowling', 'United Kingdom'),

('Harper Lee', 'United States');

-- Insert entries into the books 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 present 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 illustrates how the table with the author ID and their corresponding books will appear after creation.

What is a JOIN in SQL?

A JOIN in SQL is utilized to merge two or more tables by using conditions or matching records between them. There exists a variety of JOIN types in SQL.

Types of JOINs in SQL:

• INNER JOIN
• OUTER JOIN
• LEFT OUTER JOIN
• RIGHT OUTER JOIN
• FULL OUTER JOIN

INNER JOIN in SQL

An INNER JOIN is a specific type of JOIN in SQL that retrieves only the corresponding rows between the tables. It will exclude rows if there are no matches present in the tables.

Example:

-- INNER JOIN (Only Corresponding 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 extracts book titles alongside their respective author’s name from both tables.

LEFT JOIN in SQL

The LEFT JOIN in SQL obtains all records from the left table alongside the corresponding records from the right table. In instances where no match is identified, NULL values are returned from the right table.

Example:

-- Create an authors 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 illustrate missing authors
    price DECIMAL(10,2),
    FOREIGN KEY (author_id) REFERENCES authors(author_id) ON DELETE SET NULL
);
-- Insert entries 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 entries 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 (Displays all authors, regardless of 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:

Clarification: The left join obtains all entries from the left table (authors) along with the corresponding entries from the right table (books). If there’s no corresponding entry, then it will display as no book.

RIGHT JOIN in SQL

The right join in SQL fetches all information from the right table while securing the related information from the left table. When there is no coinciding entry, NULL values are produced from the left table.

Sample:

-- Generate an authors' table
CREATE TABLE authors (
    author_id INT IDENTITY(1,1) PRIMARY KEY,
    name NVARCHAR(100) NOT NULL,
    country NVARCHAR(50)
);
-- Generate books table (Permitting NULL for author_id)
CREATE TABLE books (
    book_id INT IDENTITY(1,1) PRIMARY KEY,
    title NVARCHAR(200) NOT NULL,
    author_id INT NULL,  -- Permitting NULL to capture books without authors
    price DECIMAL(10,2),
    FOREIGN KEY (author_id) REFERENCES authors(author_id) ON DELETE SET NULL
);
-- Add entries into the authors' table
INSERT INTO authors (name, country) VALUES
('George Orwell', 'United Kingdom'),
('J.K. Rowling', 'United Kingdom'),
('Harper Lee', 'United States');
-- Add entries 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);
-- Include a book with NULL author_id (To accommodate books without authors)
INSERT INTO books (title, author_id, price) VALUES
('Wings of Fire', NULL, 10.00); -- Now permitted due to NULL author_id
-- RIGHT JOIN Query (Displays all books, even those without 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;

Result:

Clarification: This returns all the entries from the right table (books) and fetches corresponding entries from the left table (authors). If there are no details about the author, the display will read No Author.

FULL JOIN in SQL

The full join in SQL retrieves all information from both tables, encompassing the null values.

Sample:

-- Generate an authors' table
CREATE TABLE authors (
    author_id INT IDENTITY(1,1) PRIMARY KEY,
    name NVARCHAR(100) NOT NULL,
    country NVARCHAR(50)
);
-- Generate books table (Permitting NULL for author_id)
CREATE TABLE books (
    book_id INT IDENTITY(1,1) PRIMARY KEY,
    title NVARCHAR(200) NOT NULL,
    author_id INT NULL,  -- Permitting NULL for books without authors
    price DECIMAL(10,2),
    FOREIGN KEY (author_id) REFERENCES authors(author_id) ON DELETE SET NULL
);

-- Add entries 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'); 
-- Enter 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);
-- Include a book with NULL author_id (To allow books lacking 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, Also 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;

Result:

Clarification: The full join retrieves all data from both tables, encompassing book titles and prices, including null values within them.

What is a Subquery in SQL?

A subquery is an inner query comprised within another nested SQL query. The inner query is always executed first, followed by the outer query.

Sample:

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');

Result:

Clarification: The subquery obtained the records 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 specific type of subquery in SQL that returns only a single value. It utilizes SELECT, WHERE, and HAVING clauses during its operations.

Sample:

SELECT 
    LEFT(title, 20) AS title,  
    CAST(price AS DECIMAL(6,2)) AS price 
FROM books
WHERE price = (SELECT MAX(price) FROM books);

Result:

Clarification: The scalar subquery utilized WHERE and SELECT queries to retrieve the most expensive books from the database.

2. Single-Row Subquery in SQL

The single-row subquery in SQL retrieves a singular row containing one or multiple columns. It employs operators such as =, >, =, and

Sample:

SELECT 
    LEFT(name, 15) AS author_name  
FROM authors 
WHERE author_id = (SELECT author_id FROM```sql
 books WHERE price = (SELECT MIN(price) FROM books));

Output:

Explanation: The single-row operator utilized the “=” operator to retrieve the author name of the least expensive book.

3. Multi-Row Subquery in SQL

The multi-row subquery in SQL produces multiple rows but restricts to a single column, utilizing operators such as 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 leverages the “IN” operator to identify books authored by writers located in the United Kingdom.

4. Correlated Subquery in SQL

A correlated subquery in SQL yields results based on the outer query for each row it processes, providing corresponding outcomes.

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 retrieved prices that exceeded the average price of all books authored by the same writer.

Differences Between JOIN and Subquery in SQL

Performance Considerations

• Joins are generally more efficient compared to subqueries, leveraging indexes and optimized joining engines to deliver effective output.
• Subqueries tend to be less efficient due to the potential need for correlated subqueries that might require multiple executions.

Best Practices

• Utilize joins most frequently as they are more efficient and simplify handling large datasets.
• Ensuring proper indexing will make the execution of joins for column filtering easier.
• Use subqueries for filtering values prior to using aggregate functions.
• Avoid correlated subqueries when unnecessary, as they execute once per row, which can hinder performance execution time.

When to Use JOIN vs. Subquery

JOINS

• To extract data from multiple tables.
• To optimize performance with large datasets.
• Joins offer various types, including inner, outer, full, right, and left joins to cater to differing performance needs.

SUBQUERY

• To formulate straightforward and comprehensible queries.
• To filter data prior to merging it from numerous tables.
• To execute operations using multiple aggregate functions for effective results.

Conclusion

Joins are superior in performance efficiency and adeptness in optimizing large datasets, while subqueries remain more readable and facilitate simpler queries for data filtering. Joins integrate tables, whereas subqueries cater to nested queries. For enhanced performance and scalability, prioritize JOINs; for filtering or aggregation, consider utilizing subqueries.

To delve deeper into SQL functionalities, explore this SQL course and check out SQL Interview Questions curated by industry professionals.

SQL Join vs Subquery – FAQs

The article SQL Join vs Subquery was originally published on Intellipaat Blog.

“`