Subquery

Learn to create Subquery with Kuika.

Subquery in FROM clause

A subquery with FROM clause acts similar to a gradeorary table that has been generated during the execution of a query then lost afterwards.

TEACHERS.Id ,
Students.Grade FROM (
SELECT Id
FROM Students
WHERE TeacherId IS NULL
) AS Teachers
JOIN Students ON Teachers.Id=Studets.Id

Subquery in SELECT clause

SELECT
Id,
FName,
LName,
(SELECT COUNT(*) FROM Bikes WHERE Bikes.CustomerId=Customers.Id) AS NumberOfBikes
From Customers

Subquery in WHERE clause

Use a subquery to filter the result set. For example this will return all students with a grade equal to the highest graded student.

SELECT *
FROM Students
WHERE StudentID not in (SELECT StudentID FROM Prefects)

Correlated Subqueries

Correlated (might be known as Synchronized or Coordinated) Subqueries are the queries that make references to the current row of their outer query:

SELECT StudentId
FROM Sudent AS e0uter
WHERE Grade > (SELECT AVG(Grade)
FROM Student eInner
WHERE eInner.ClassId =e0uter.ClassId
)
Subquery SELECT AVG(Grade) ... is correlated because it refers to student row e0ter from its outer query

Filter query results using query on different table

This query selects all students not on the prefects table.

SELECT *
FROM Students
WHERE StudentID not in (SELECT StudentID FROM Prefects)

You can get the same results with a LEFT JOIN.

SELECT * 
FROM Students AS e
LEFT JOIN Prefects AS s ON s.StudentID=e.StudentID
WHERE s.StudentID is NULL

Subqueries in FROM clause

You are able to use subqueries to create a gradeorary table and use it in the FROM clause of an "outer" query

SELECT * FROM (SELECT student, grade_hi - grade_lo AS grade_;var FROM exam) AS w 
WHERE grade_var >20;

The above finds students from the grades table whose exam grade variation is greater than 20.

Subqueries in WHERE clause

The following example finds students (from the students example) whose participation is below the average grade (obtained via a sub-qquery):

SELECT name, participation FROM students
WHERE participation <(SELECT avg(partiğcipation) FROM students);s