Advanced SQL Queries
-
Learn advanced SQL techniques for complex database queries and optimization.
GROUP BY
What is GROUP BY?
GROUP BY is used to group rows with the same values and apply aggregate functions like:
COUNT()
SUM()
AVG()
MIN()
MAX()
Example Table: students
Example 1: Average Marks by Subject
Using GROUP BY with AVG in SQL
This SQL query calculates the average marks for each subject in the students table using the AVG() function and groups the results by subject with GROUP BY.
SELECT subject, AVG(marks) AS average_marks
FROM students
GROUP BY subject;Groups data by subject and calculates average marks.
Example 2: Count Students per Subject
Using GROUP BY with COUNT in SQL
This SQL query counts the total number of students in each subject from the students table using the COUNT() function and groups the results by subject with GROUP BY.
SELECT subject, COUNT(*) AS total_students
FROM students
GROUP BY subject;HAVING
What is HAVING?
HAVING is used to filter grouped data.
Important:
WHERE filters rows
HAVING filters groups
Example: Show Subjects with Avg Marks > 80
Using HAVING Clause with GROUP BY in SQL
This SQL query displays subjects whose average marks are greater than 80. It uses GROUP BY to group data by subject and HAVING to filter groups based on the AVG(marks) condition.
SELECT subject, AVG(marks) AS average_marks
FROM students
GROUP BY subject
HAVING AVG(marks) > 80;JOIN
What is JOIN?
JOIN is used to combine data from multiple tables based on a related column.
Example Tables
students
marks
Types of JOIN
INNER JOIN
Returns matching records from both tables.
Using INNER JOIN in SQL
This SQL query uses INNER JOIN to retrieve matching records from both the students and marks tables based on the student_id. It returns only the records where a match exists in both tables.
SELECT students.name, marks.subject, marks.marks
FROM students
INNER JOIN marks
ON students.student_id = marks.student_id;Only matched student IDs are shown.
LEFT JOIN
Returns all records from left table + matched from right table.
Using LEFT JOIN in SQL
This SQL query uses LEFT JOIN to return all records from the students table (left table) and the matched records from the marks table (right table). If no match is found, NULL values are returned for the right table columns.
SELECT students.name, marks.subject
FROM students
LEFT JOIN marks
ON students.student_id = marks.student_id;Example Tables
students
marks
Notice:
Student ID 3 has no marks
Student ID 4 exists in marks but not in students
RIGHT JOIN
Returns all records from right table + matched from left table.
What is RIGHT JOIN?
RIGHT JOIN returns:
All records from right table
Matching records from left table
If no match → NULL from left table
Example Query:
Using RIGHT JOIN in SQL
This SQL query uses RIGHT JOIN to return all records from the marks table (right table) and the matched records from the students table (left table). If no match is found, NULL values are returned for the left table columns.
SELECT students.name, marks.subject, marks.marks
FROM students
RIGHT JOIN marks
ON students.student_id = marks.student_id;Output:
Why NULL?
Because student_id = 4 exists in marks but not in students.FULL JOIN
Returns all records when there is a match in either table.
(Note: Not supported directly in MySQL)What is FULL JOIN?
FULL JOIN returns:
All records from left table
All records from right table
If no match → NULL on missing side
Example Query (PostgreSQL):
Using FULL JOIN in PostgreSQL
This PostgreSQL query uses FULL JOIN to return all records from both students and marks tables, including matched and unmatched rows from both sides.
SELECT students.name, marks.subject, marks.marks
FROM students
FULL JOIN marks
ON students.student_id = marks.student_id;Output:
Explanation:
Amit (ID 3) has no marks → NULL values
English (ID 4) has no student → NULL name
FULL JOIN in MySQL
⚠ MySQL does NOT support FULL JOIN directly.
We simulate it using:
Simulating FULL JOIN in MySQL
Since MySQL does not support FULL JOIN directly, this query combines LEFT JOIN and RIGHT JOIN using UNION to return all records from both tables, including unmatched
SELECT students.name, marks.subject, marks.marks
FROM students
LEFT JOIN marks
ON students.student_id = marks.student_id
UNION
SELECT students.name, marks.subject, marks.marks
FROM students
RIGHT JOIN marks
ON students.student_id = marks.student_id;Quick Comparison