SQL Subqueries

Hello, and welcome to the blog. In this blog we are going to talk about "Subqueries".

In the previous blog we have seen small topic of ALL, IN, ANY operators. Now we will see how write a query inside a query. Don't worry I will elaborate how we are gonna do it.

So, we are going to talk about

1. What is Subquery?
2. Rules of writing outer query.
3. Rules of writing subquery.
4. Basic types of Subquery.
5. Correlated Subquery.
6. Possible number of nesting in Subquery
7. Subquery in Update, Delete and Insert Statement
8. Subquery with Exists keyword.

let's jump into it

1) What is a subquery

1. Subquery is a query inside a query or you can say it as nested query.
2. It is mainly used as a part of where clause.
3. Subquery returns data that will be used in the main query.
4. Subquery can be used with SELECT, INSERT, UPDATE and DELETE statements.
5. One more thing about subqueries is that subqueries can be alternatively formulated as JOINS.
6. Let's see a subquery :
   SELECT FirstName
   FROM Employee1
   WHERE DepartmentId=(
        SELECT Id
        FROM Department
        WHERE DepartmentName='IT'
        )

   

   
   
7. As I said subqueries can be formulated as JOINS, let's see how we can do it with joins:
   SELECT FirstName
   FROM Employee1 e
   INNER JOIN Department d ON e.DepartmentID=d.Id
   WHERE d.DepartmentName='IT'

   

   
   
8. As you can see we got the same result

2) Rules for writing outer query of sub query

1. A regular SELECT query including regular select list component.
2. A regular FROM clause including one or more tables or views.
3. optional WHERE clause.
4. optional HAVING clause.
5. optional GROUP BY clause.

3) Rules of Subquery

1. The SELECT list of a subquery introduced with a comparison operator can include only one expression or column name(except that 'exists' and 'in' operator on select or a list).
2. If where clause includes a column name it must be join compatible with column in subquery list.
3. The ntext, text and image data types of SQL cannot be used in SELECT list of subqueries.
4. Subquery must return a single value subqueries which are introduced without ANY or ALL keyword cannot include 'GROUP BY' or 'HAVING' clause.

4) Basic Subquery types

1. Operator on list introduced with IN or comparison operator modified by ANY or ALL which we already know.
2. Introduced with an unmodified comparison operator and must return a single value.
3. Existence test introduced with exists.

5) Correlated Subquery

1. Many queries can be evaluated by executing a subquery once and substituting the resulting value or values to the main query.
2. In correlated subquery, the subquery depends on the outer query for its value.
3. This means that the subquery is executed repeatedly, once for each row that might be selected by the outer query.
4. Let's understand this concept with example
5. Example: Find all the Employees who earns more than average salary in their respective department.
6. Solution: 
   SELECT FirstName, Salary, DepartmentName
   FROM Employee1 e1
   INNER JOIN Department d on e1.DepartmentId=d.ID
   WHERE SALARY>(
        SELECT avg(Salary)
        FROM Emploee1 e2
        WHERE e1.DepartmentId=e2.DepartmentId
        GROUP BY DepartmentId
        )

   

   
   
7. Here in this query value of DepartmentId in subquery should match with DepartmentId in outer query that is why this query is "Correlated Subquery".

6) How many levels of nesting is possible?

1. In SQL Server 32 levels of nesting is possible, this limit also varies depending upon available memory and complexity of memory.
2. When it comes to oracle database. It imposes no limit on the number of subquery level in the FROM clause of top-level query. You can nest up to 255 levels of subqueries in where clause.

7) Subqueries in Update, Delete and Insert Statement.

7.1. Update Statement

1. Let's see a subquery in Update statement. This query might feel a little illogical to you but my point here is to explain 
   1. Subquery in Update Statement
   2. Multiple level of nesting 
   Data before running query

   

   
   

   UPDATE Employee
   SET Salary = Salary * 1.3
   WHERE EmployeeId IN (
   		SELECT Id
   		FROM Employee
   		WHERE DepartmentId = (
   				SELECT Id
   				FROM Department
   				WHERE DepartmentName = 'IT'
   				)
   		)

   

   
   

2. You can solve this query easily, I purposely made it complex. Easier version of this query is:
   Data before running query:

   

   
   
   UPDATE Employee1
   SET salary =salary*1.3
   WHERE DepartmentId=3

   

   
   

7.2. Subqueries in Insert Statement

1. In insert statement when subquery is used the main purpose is to insert data into the table which is returned by subquery.
2. Whatever data is returned from the query is inserted into the table
3. Let's see a example:
   INSERT into
   Employee2 (FirstName, LastName, Department, Salary, Designation, ReportingManager)
   SELECT FirstName, LastName, Department, Salary, Designation, ReportingManager
   FROM Employee1
   WHERE DepartmentId=2

   

   
   
4. This kind of insert is always done when you want to perform some critical operation which may lead to data loss. So to create backup of data a new "Backup Table" is generated with this kind of query.
5. As we we are going for delete statement next this is the best time to take a backup.

7.3. Subqueries with DELETE statement

1. Subquery with delete statement is very similar to what we have seen in UPDATE statement.
2. Let's see a example:
   DELETE from Employee1
   WHERE DepartmentId=(
   SELECT Id
   FROM Department
   WHERE DepartmentName='Sales')

   

   
   

8) Subquery with Exists keyword

1. When subquery is introduced with keyword "EXISTS" the subquery act as existence test.
2. It check whether a record exists or not and returns True or False accordingly.
3. Example:
   SELECT FirstName
   FROM Employee1 e
   WHERE EXISTS(
   SELECT *
   FROM Department
   WHERE Id=e.DepartmentId)

   

   
   

Here we end Subquery. I know its little big but very informative and Congratulation's on learning subqueries.

See you in some other blog till then "Keep coding, keep learning because trust me you can make it big".

"Have a great day"