Subqueries in SQL

Sub quires are easy to understand and can be developed by using simple quires. These queries will have two parts, one is inner queries or inner select and outer queries or outer select.

These queries are easy to develop and most of the time preferred over complex joins.

There is some restriction in using subqueries. We cant modify the structure of a table by using a subquery.

Let us start with some examples by using different types of queries using various SQL commands.
SELECT * FROM student WHERE name LIKE
 (SELECT name FROM student WHERE mark =86)
idnameclassmark
28Rojj BaseSeven86

Subquery with not in

SELECT * FROM student WHERE  class NOT IN 
(SELECT DISTINCT(class) FROM  student)
This query will return if any record is there where class data is not there.

Subquery By using ANY

SELECT  * FROM student WHERE  
 class = ANY(SELECT DISTINCT(class) FROM student)
We will get all the records as we don't have any blank class data
SELECT * FROM student WHERE 
 mark =  (SELECT MAX(mark) FROM  student)
idnameclassmark
33Kenn ReinSix96
The above query will return the details of the student who got maximum mark. This query will also work if there are two students with same maximum mark. But if we use only Max command then we will get only one record.

Subquery with Not In

SELECT  * FROM student WHERE class NOT IN 
 (SELECT MAX(mark) FROM student WHERE class= ANY(SELECT DISTINCT class FROM student))

Using Union and NOT IN

We have one student table and two games, table football and baseball. Some students are selected for the football team and for the baseball team.

Find out the students who are not selected for any of the teams. Or find the records in the student table which doesn't have a matching record in the football table or baseball table.
SELECT * FROM `student` WHERE id NOT IN 
(SELECT f_id from student_football UNION select b_id from student_baseball)
You can get similar result by using LEFT JOIN of three tables.

Download the SQL dump of our student table
SQL References Selecting records SQL LEFT Join to link tables

Subscribe to our YouTube Channel here


Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com
    mallamma.b.hataraki

    02-08-2010

    please can you post more subqueries examples to retrieve data from more than one table.
    Noel

    13-04-2012

    How to put a total at the bottom of a detailed report. for example query database Select product_code,description,net_sales from tablex how do you put a total at the end of the report below column net_sales.
    rajavel

    31-05-2014

    awesome explain;;;;
    tgfughjgu

    10-07-2014

    [ll'w to put a total at the bottom of a detailed report.
    for example query database
    Select product_code,description,net_sales from tablex

    how do you put a total at the end of the report below column net_sales.
    rajavel 31-05-2014
    Abdulsalam Saidu

    10-12-2016

    ...Please more example on the subquery...

    Post your comments , suggestion , error , requirements etc here





    SQL Video Tutorials










    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2024 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer