Search⌘ K
Home
Courses
SQL Interview Preparation

Answer: Subquery in the SELECT Clause

Understand how to use subqueries within the SELECT clause to fetch related data from different tables. Learn to apply aliases, filtering with WHERE clauses, and explore alternative solutions involving LEFT, RIGHT, and INNER JOINS to write efficient SQL queries.

We'll cover the following...

Solution

The solution is given below:

MySQL
/* The use of subquery to find the employees working on a project */
SELECT (SELECT e.EmpName FROM Employees AS e WHERE e.EmpID = p.EmpID) AS EmpName, 
        ProjectName
FROM   Projects AS p
WHERE  p.EmpID IS NOT NULL;

Explanation

The explanation of the solution code is given below:

  • Lines 2–3: The SELECT query selects EmpName (coming from the subquery) and ProjectName from Projects, respectively. The e.EmpName refers to the EmpName column from the Employees table (aliased as e) and  p.EmpID refers to the EmpID column from the Projects table (aliased as p). 

  • Line 4: The data is retrieved from the Projects table.

  • Line 5: The WHERE clause ensures that only those ...