K-Learn

SQL Sub Query

Posted on Friday, 10 March 2023 10:58 AM

A Sub Query, also known as an inner query or nested query, is a query that is embedded within another query. It is enclosed in parentheses and typically appears in the WHERE or HAVING clause of the outer query.

Sub Query Syntax

SELECT column1, column2, …
FROM table1
WHERE column1 operator (SELECT column1 FROM table2 WHERE condition);

For example,

SELECT *
FROM student
WHERE id IN (
  SELECT student_id
  FROM exam
  WHERE subject_id = (
    SELECT id
    FROM subject
    WHERE name = 'Maths'
  )
);

OUTPUT

In the given example,

Let's say we want to find all the students who have taken exams in the subject 'Maths'. 

This query first selects all the rows from the student table. Then it uses a subquery to filter the results based on the id column.

The subquery selects all the rows from the exam table where the subject_id column matches the id value of the 'Maths' subject. This ensures that we are only considering exams taken in that subject.

Then, the subquery returns the student_id column from the exam table. This is used as the filter criteria for the outer query's WHERE clause.

The outer query checks whether the id column of each row in the student table matches any of the values returned by the subquery. If a match is found, the corresponding row from the student table is returned in the result set.

It's important to note that subqueries can be used in various ways, such as in the SELECT clause, in the FROM clause, or even in another subquery. The subquery can also use any valid SQL statement, such as SELECT, INSERT, UPDATE, or DELETE.