How Do SQL Joins and Subqueries Differ?

TL;DR

Use a nested subquery to produce department employee IDs first, then filter employee rows with IN. A correlated subquery instead checks each employee row against the department table and uses EXISTS to keep rows with a matching ID. A join also relates the tables through their common employee ID, allowing the same employee-detail question to be expressed another way.

Transcript

Hello friends! You are welcome to Gate Smashers In this video we are going to discuss very important concept in SQL And that concept you will not get one the whole youTube in one video And actually what is this concept That one query only, with nested subquery That one only with correlated And that only with the help of join we are going to write W... Read More

Key Insights

• A nested subquery is a query written inside another query, with an inner query and an outer query. In the demonstrated approach, execution proceeds from the inner query to the outer query, which is described as a bottom-up process.
• The sample database has an Emp table for employee details and a Dept table for department information. Employee ID is the common attribute that connects them, allowing information from both tables to contribute to the requested result.
• The target query is to find complete details for every employee who works in any department. Since complete employee details belong to Emp and department membership belongs to Dept, the solution needs information drawn from both tables.
• The nested inner query SELECT E_id FROM Dept returns employee IDs 1, 2, and 3 in the example. It executes once, after which the outer query tests each employee record against the returned collection of IDs.
• The IN operator is appropriate when one employee ID must be compared with several values returned by a subquery. Equality is presented as suitable when the inner query returns only one answer, while IN handles the demonstrated group of IDs.
• The correlated subquery is a top-down process that begins with a row from the outer Emp table. Its inner condition references both tables through Emp.E_id and the department employee ID, creating the relationship that makes the query correlated.
• The EXISTS condition works with true or false results. If an employee ID matches any department row, EXISTS is true and that employee is returned; if no match exists, it is false and the employee is excluded.
• The three query forms are intended to answer the same question by relating Emp and Dept through employee ID. The nested form builds an ID set first, the correlated form checks outer rows individually, and the join form uses the common attribute directly.

Questions & Answers

Q: How does a nested SQL subquery find employees assigned to departments?

The nested solution uses SELECT * FROM Emp WHERE E_id IN (SELECT E_id FROM Dept). The inner query executes first and returns the department table's employee IDs, which are 1, 2, and 3 in the example. The outer query then checks every Emp row against those values and returns complete details for the matching employees.

Q: What is the difference between nested and correlated SQL subqueries?

A nested subquery follows the demonstrated bottom-up approach: the inner query executes once, produces its output, and the outer query uses that output. A correlated subquery follows a top-down approach: it takes one outer Emp row at a time and runs an inner comparison that references the outer employee ID alongside the department table.

Q: Why is employee ID needed in both SQL tables?

Employee ID is the common attribute connecting the Emp and Dept tables. Emp contains employee details, while Dept identifies departments and the employees working in them. The shared ID makes it possible to relate a person's complete employee record to department membership. Without something common, the two sets of information cannot be meaningfully related in the demonstrated query.

Q: When should IN be used instead of equality in a subquery?

IN should be used when the inner query returns a group of values and one outer value must be compared with every member of that group. In the example, SELECT E_id FROM Dept returns 1, 2, and 3, so each Emp employee ID is tested with IN. Equality is described as appropriate when only one answer is returned.

Q: How does EXISTS work in a correlated SQL subquery?

EXISTS evaluates whether the correlated comparison produces a true or false result for an outer employee row. The query takes an Emp row and compares its employee ID against department records. If any department employee ID matches, EXISTS becomes true and the employee is printed. If no department row matches, the condition is false and that employee is omitted.

Q: Which employees are returned by the example SQL queries?

Employees with IDs 1, 2, and 3 are returned because those IDs appear in the Dept table and therefore represent employees working in departments. Their names are A, B, and C in the example. Employees 4 and 5 are excluded because no department has yet been assigned to them in the presented data.

Q: Why does the employee query select all columns from Emp?

The request asks for complete employee details rather than only employee IDs. Those details are stored in the Emp table, which is described as potentially containing names, addresses, mobile numbers, dates of birth, and other information. Therefore, SELECT * is used so every available employee attribute is returned for each employee whose ID appears in a department record.

Q: How can joins, nested subqueries, and correlated subqueries answer the same SQL question?

All three forms use the relationship between Emp and Dept to identify employees assigned to departments. The nested subquery first obtains department employee IDs and filters Emp with IN. The correlated form checks each outer employee against Dept and uses EXISTS. The join form relates the two tables through their common employee ID to produce the same requested employee details.

Summary & Key Takeaways

• The example uses an Emp table containing employee IDs and names, plus a Dept table containing department numbers, department names, and employee IDs. Employees 1, 2, and 3 have department records, while employees 4 and 5 do not yet have assigned departments, so the expected result contains the first three employees.

• The nested solution is SELECT * FROM Emp WHERE E_id IN (SELECT E_id FROM Dept). It follows a bottom-up process: the inner query runs once and returns employee IDs 1, 2, and 3. The outer query then compares every Emp row with that result and returns all columns for matching employees.

• The correlated solution follows a top-down process. It takes one row from the outer Emp table, compares its employee ID with department rows inside the subquery, and relies on a true or false result from EXISTS. Rows for employees 1, 2, and 3 succeed, while employees 4 and 5 fail.