Links

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.
SELECT 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
Last modified 8mo ago