YogeshChauhan.com
A Quick Comparison of JOIN and Subquery in SQL
January 6, 2020

A subquery is simply query inside a query. Nested. While JOIN is like a merger of two tables to form a new table virtually. It combines data from two different tables or the table itself(self-join).

We can get many different results from using Subquery and JOIN which, many times, can't be done by simple SQL query. But most of the subqueries can be rewritten using JOIN and vice versa. Let's take a look at the examples.

Let's take a look at the examples to understand this better.


//JOIN example

SELECT invoice_number, invoice_date, invoice_total FROM invoices JOIN sellers
ON invoices.seller_id = sellers.seller_id
WHERE seller_state='FL'
ORDER BY invoice_date;

The query above is fetching the number, date and total for each invoices and joins with sellers on sellers_id. Also, we have a condition in WHERE which will limit the result set to Florida sellers only.

Now take a look the following query.


//Subquery example

SELECT invoice_number, invoice_date, invoice_total FROM invoices
WHERE seller_id IN
(SELECT seller_id FROM sellers WHERE seller_state= 'FL')
ORDER BY invoice_date;

In the first query I have used JOIN to combine the sellers and invoices data and then I am checking for the state using WHERE condition.

In the second query, I have added a subquery in which it will only return the seller_id of Florida sellers. So, in that query I am fetching the data for only Florida sellers from invoices using WHERE condition.

So, what should we use?

I think we should use the query based on the complexity and relationship between the tables as well. But that's my opinion.

JOIN is more helpful when there is a relationship between tables like the query above. They both have seller_id column in common. Subquery is helpful whether there is a relation or not (ad hoc relationship we can say).

For complex queries subqueries are easier to use and divide the query in small parts and analyze from human eyes (more readable, more understandable). We don't always find relationships between all tables in complex queries so using subqueries might be a good idea. 

Advantages of JOIN

  • It included columns in result set from both tables.
  • More helpful/intuitive when there is a relationship between tables.

Advantages of Subquery

  • We can pass aggregate value using subquery
  • More helpful/intuitive when there is no relationship or more like ad hoc relationship
  • It makes complex and lengthy queries easier to write, more readable and understandable
dreamhost

Leave a Reply

Most Read

#1 How to check if radio button is checked or not using JavaScript? #2 How to set opacity or transparency using CSS? #3 Pagination in CSS with multiple examples #4 How to make HTML form interactive and using CSS? #5 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #6 How to uninstall Cocoapods from the Mac OS?

Recently Posted

Mar 4 How to use data-* Attributes in HTML? Mar 4 The substr() method in JavaScript and how it’s different from substring() Mar 4 A complete guide to add responsive YouTube videos using HTML and CSS Mar 3 How to embed YouTube or other video links in WordPress? Mar 3 How to change the Login Logo in WordPress? Mar 3 substring() Method in JavaScript

You might also like these

How to import MySQL small sample database into phpMySQL?SQL/MySQLA Step by Step Guide to Make RSS in XML For Any Website or Blog For FreeMisc4 different ways to create JavaScript ObjectsJavaScriptHow to Clone Objects in PHP?MiscWhat is the difference between let and var in Swift?SwiftArray destructuring and Object destructuring in JavaScriptJavaScript