What is Sub-Query ? what is a use of sub query?


#1

What is a Sub-Query?


#2

SELECT *
FROM CUSTOMERS
WHERE ID IN (SELECT ID
FROM CUSTOMERS
WHERE SALARY > 4500) ;

In this “SELECT ID
FROM CUSTOMERS
WHERE SALARY > 4500” is the subquery which will return a table like

ID
1
2
3
8
10

Now the outer query select * from customers in(1,2,3,8,10) will run to give the final result


#3

sub-query is used to structure queries. it is like having a query within another query.
example-
Let’s say that we have a table called employee with columns employee_name, last_name, employee_salary, and employee_number. And we also have another table called department that has columns called manager_employee_number and department_name.
Query-
select employee_name from employee
where employee_salary( select avg(employee_salary) from employee) // this is sub-query


#4

but i have read that subquery reduces speed,
Can I always convert a query involving a subquery into another equivalent query which doesnt not have any subquery


#5

A subquery is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery. A subquery can be used anywhere an expression is allowed. In this example a subquery is used as a column expression named


#6

A sub-query is also called an inner query or a nested query is embeded within the WHERE clause.

A subquery is used to return the data that will be used in main query as a condition to further restrict the data to be retrieved.

Subqueries can be used with the SELECT,INSERT,UPDATE, and DELETE statements along with the operators like =,<,>,>=,>=,IN,BETWEEN,etc.

There are few rules that subqueries must follow-

  • subqueries must be enclosed within paranthesis.
  • A subquery can have only one coloumn in the SELECT clause,unless multiple coloumns are in main query for the subquery to compare its selected coloumns.
  • An ORDER BY command cannot be used in a subquery,although the main query can use an ORDER BY.The GROUP BY command can be used to perform the same function as the ORDER BY in a subquery.
  • Subqueries that return more than one row can only be used with multiple value operators such as the N operator.
  • The SELECT list cannot introduce any references to values that evaluate to a BLOB,ARRAY,CLOB, or NCLOB.
  • A subquery cannot be immediatley enclosed in a set function.
  • The BETWEEN operator cannot be used with a subquery.However, the BETWEEN operator can be used within the subquery.

Use of a subquery:

  • A subquery can be used anywhere an an expression is allowed .

  • we can use sub queries in the UPDATE statement as below:

    UPDATE EMPLOYEE
    SET SALARY =SALARY + (SALARY * 0.1)
    WHERE DEPT_ID = (SELECT DEPT_ID FROM DEPT WHERE _NAME =‘DESIGN’);

The above query is written to update the salary of DESIGN employees by 10%.

  • we can use subquery in the DELETE statement as below:

    DELETE EMPLOYEE
    WHERE DEPT_ID = (SELECT DEPT_ID FROM DEPT WHERE DEPT_NAME=‘DESIGN’);

  • Subqueries can be used to compare the values using SOME clause .SOME clause is used when we have to compare the list of values using OR clause.if any one in the list value is matching,the result will be displayed.

  • subqueries can be used to compare the values using ALL clauseALL clause is used when we have to compare the list of values using AND clause.

  • subqueries can be used along with UNIQUE clause to check if the subquery returns duplicant values.