SQL Sub-Query

➣  Sub-queries are queries embedded in queries.

➣  They are used to retrieve data from one table based on data in another table.

➣  Generally used when tables have some kind of relationship.

➣  Meaning a sub-query SELECT statement can standalone and is NOT depended on the statement in which it is nested.

The sub-query can contain any valid SELECT statement, but it must return a single column with the expected number of results.

IF the sub-query returns only one result, then the main query can check for equality, inequality, greater than, less than, etc.
On the other hand, if the subquery returns more than one record, the main query must check to see if a field value is (or is NOT ) IN the set of values returned.

FOR example in database, the Orders Table has a customer_id field, which references a customer in the Customers Table. Thus, retrieving data for a specific order is pretty straightforward.

Detailed Examples :

Note:
c = alias for customers
o = alias for orders

♦ Let's start with a simple SQL Left Join Query.

SELECT * FROM customers c
LEFT JOIN orders o
on c.id = o.customer_id
WHERE o.product = 'wiper';

Step by Step Process :

1. Selecting all the data from Customers Table with matched data in Orders Table (see 1-a Table).
2. Joining/merging the data from Orders Table where ID (customers table) is EQUAL to CUSTOMER_ID (orders table) (see 2-a Table).
3. Filter the results by (WHERE) clause, product (orders table) is EQUAL to ' wiper ' (see 3-a Table).

CONCLUSION: Our first query gathers all the data first by executing the SELECT, JOIN functions, then by setting the WHERE clause at the end of query will filter the data given by the first executed functions.

♦ Next, let's do the SQL Join with Sub-Query.

SELECT * FROM customers c
LEFT JOIN ( SELECT * FROM orders o WHERE o.product = 'wiper' ) as sub_result
on c.id = sub_result.customer_id;

Step by Step Process :

1. Selecting all the data from Customers Table with matched data in Orders Table (see 1-b Table).
2. Selecting all the data from Orders Table where product (orders table) is EQUAL ' wiper ' (see 2-b Table).
3. Joining/merging data from Step1 and Step2 with filter clause (see 3-b Table) .
   3-1: Filter the results by (WHERE) clause statement.
   3-2. All data from Step1 will remain, appending the data from Step2 if matched, id (customers table) is EQUAL to customer_id (orders table).
   3-3. All data from Step1 with NO matched, will append and set to null value .

CONCLUSION: Our second query gets all the data from the Customers Table, but before joining the result from Orders Table there's another function called SUB-QUERY (a independent/standalone query) which filters the data and sending it to a variable which allows us to be used for further execution like join function.

Happy Programming Everyone !!!