Home Mysql Mysql5 Writing Subqueries
Questions:Ask|Latest

 
 

Share on Google+Share on Google+

Writing Subqueries

Advertisement
A subquery can be defined as a query within a query. In other words, any query results that we reuse in another query. Subquery is known as nestee queries or subselects also. Subqueries don?t include any new functionality but the queries are more readable

Writing Subqueries

     

A subquery can be defined as a query within a query. In other words, any query results that we reuse in another query. Subquery is known as nestee queries or subselects also. Subqueries don?t include any new functionality but the queries are more readable with using subqueries rather than of joins.

We will describe you the subqueries with the help of following tables : 

mysql> SELECT * FROM Client;
+------+---------------+----------+
| C_ID | Name          | City     |
+------+---------------+----------+
| 1    | A K Ltd       | Delhi    |
| 2    | V K Associate | Mumbai   |
| 3    | R K India     | Banglore |
| 4    | R S P Ltd     | Kolkata  |
| 5    | A T Ltd       | Delhi    |
| 6    | D T Info      | Delhi    |
+------+---------------+----------+
6 rows in set (0.08 sec)
mysql> SELECT * FROM Products;
+---------+-------------+------+----------+
| Prod_ID | Prod_Detail | C_ID | price    |
+---------+-------------+------+----------+
| 111     | Monitor     | 1    | 7000.00  |
| 112     | Processor   | 2    | 11000.00 |
| 113     | Keyboard    | 2    | 1200.00  |
| 114     | Mouse       | 3    | 500.00   |
| 115     | CPU         | 5    | 15500.00 |
+---------+-------------+------+----------+
5 rows in set (0.00 sec)

There are 3 basic types of subqueries in SQL: 

  • Predicate Subqueries - extended logical constructs in the WHERE (and HAVING) clause.
  • Scalar Subqueries - standalone queries that return a single value; they can be used anywhere a scalar value is used.
  • Table Subqueries - queries nested in the FROM clause.

All subqueries must be enclosed in parentheses. 

Predicate Subqueries

Predicate Subqueries can be used in the HAVING and WHERE clause only because both are special logical construct. These subqueries must retrieve one column.

  • IN Subquery
      
    The IN subquery tests if a scalar values match with the single query column value in any subquery result row. The general syntax is :
      Value_1 [NOT] IN (query_1)

    In the following example we are getting the list of clients that are available in Products table also. Example : 

    mysql> SELECT * FROM Client WHERE C_ID IN
        -> (SELECT C_ID FROM Products);
    +------+---------------+----------+
    | C_ID | Name          | City     |
    +------+---------------+----------+
    | 1    | A K Ltd       | Delhi    |
    | 2    | V K Associate | Mumbai   |
    | 3    | R K India     | Banglore |
    | 5    | A T Ltd       | Delhi    |
    +------+---------------+----------+
    4 rows in set (0.00 sec)

    In the following example we are getting the list of clients that are not available in Products table also. Example : 
     
    mysql> SELECT * FROM Client WHERE C_ID NOT IN
        -> (SELECT C_ID FROM Products);
    +------+-----------+---------+
    | C_ID | Name      | City    |
    +------+-----------+---------+
    | 4    | R S P Ltd | Kolkata |
    | 6    | D T Info  | Delhi   |
    +------+-----------+---------+
    2 rows in set (0.01 sec)
  •   
  • Quantified Subqueries 
     
     
    A quantified subquery can use the all comparison operators for several types of tests. The general syntax is :
      Value_1 {=|>|<|>=|<=|<>} {ANY | ALL | SOME} (query_1)
      

    The comparison operator is used to compare value_1 to the single query column value from each subquery result row. If we are using ALL clause then must match the all rows in subquery, or subquery must be empty. If we are using ANY or SOME clause then must match at least one row in the subquery.
    Example : 
     
    mysql> SELECT * FROM Client WHERE C_ID= ANY(SELECT C_ID FROM Products);
    +------+---------------+----------+
    | C_ID | Name          | City     |
    +------+---------------+----------+
    | 1    | A K Ltd       | Delhi    |
    | 2    | V K Associate | Mumbai   |
    | 3    | R K India     | Banglore |
    | 5    | A T Ltd       | Delhi    |
    +------+---------------+----------+
    4 rows in set (0.00 sec)
  •  
  • Exists Subqueries
     
    The EXISTS subquery is used to tests whether a subquery returns at least one row or a qualifying row exists. The general syntax is :
      Exists (query_1)
     
    Any EXISTS subquery should contain an outer reference. It must be a correlated subquery. Example : 

    mysql> SELECT * FROM Client
        -> WHERE EXISTS
        -> (SELECT * FROM Products WHERE Client.C_ID=Products.C_ID);
    +------+---------------+----------+
    | C_ID | Name          | City     |
    +------+---------------+----------+
    | 1    | A K Ltd       | Delhi    |
    | 2    | V K Associate | Mumbai   |
    | 3    | R K India     | Banglore |
    | 5    | A T Ltd       | Delhi    |
    +------+---------------+----------+
    4 rows in set (0.00 sec)

Scalar Subqueries

The Scalar Subquery is a subquery which returns a single value. A Scalar subquery can be used almost anywhere a single column value can be used. The subquery have to reference only one column in the select list. It must not retrieve more than one row. When subquery retrieve one row then the value of select list column becomes the value of the Scalar Subquery. Example : 

mysql> SELECT (SELECT Name FROM Client WHERE C_ID=1);
+----------------------------------------+
| (SELECT Name FROM Client WHERE C_ID=1) |
+----------------------------------------+
| A K Ltd                                |
+----------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT (SELECT C_ID FROM Products WHERE C_ID=2) FROM Client;
ERROR 1242 (21000): Subquery returns more than 1 row
mysql> SELECT (SELECT C_ID FROM Products WHERE C_ID=1) FROM Client;
+------------------------------------------+
| (SELECT C_ID FROM Products WHERE C_ID=1) |
+------------------------------------------+
| 1                                        |
| 1                                        |
| 1                                        |
| 1                                        |
| 1                                        |
| 1                                        |
+------------------------------------------+
6 rows in set (0.01 sec)

Table Subqueries

Table subqueries are used in the FROM Clause , replace the table name. These subqueries can have correlation name also. Example : 

mysql> SELECT Client.*,Price
    -> FROM Client, Products
    -> WHERE Client.C_ID=Products.C_ID
    -> AND Price>1000;
+------+---------------+--------+----------+
| C_ID | Name          | City   | Price    |
+------+---------------+--------+----------+
| 1    | A K Ltd       | Delhi  | 7000.00  |
| 2    | V K Associate | Mumbai | 11000.00 |
| 2    | V K Associate | Mumbai | 1200.00  |
| 5    | A T Ltd       | Delhi  | 15500.00 |
+------+---------------+--------+----------+
4 rows in set (0.06 sec)

Using Single Value Subqueries

Firstly we will start with a simple query : 

mysql> SELECT MAX(Price) FROM Products;
+------------+
| MAX(Price) |
+------------+
| 15500.00   |
+------------+
1 row in set (0.60 sec)

The above example retrieve only a single value and its representing the maximum Price of the Product. In this example we used a MySQL Function MAX() that finds the greatest values in a specified column. 

Single ? value subqueries is used to return a single column value and then they are typically used for comparison. For Example :

mysql> SELECT * FROM Client c,Products p WHERE c.C_ID=p.C_ID
    -> AND p.Price=(SELECT MAX(Price) FROM Products);
+------+---------+-------+---------+-------------+------+----------+
| C_ID | Name    | City  | Prod_ID | Prod_Detail | C_ID | price    |
+------+---------+-------+---------+-------------+------+----------+
| 5    | A T Ltd | Delhi | 115     | CPU         | 5    | 15500.00 |
+------+---------+-------+---------+-------------+------+----------+
1 row in set (0.02 sec)

In the above example we are getting the detail of products that have the highest price and the client details also.

Advertisement

If you enjoyed this post then why not add us on Google+? Add us to your Circles



Liked it!  Share this Tutorial


Follow us on Twitter, or add us on Facebook or Google Plus to keep you updated with the recent trends of Java and other open source platforms.

Posted on: March 13, 2008

Related Tutorials

Ask Questions?    Discuss: Writing Subqueries   View All Comments

Post your Comment


Your Name (*) :
Your Email :
Subject (*):
Your Comment (*):
  Reload Image
 
 
Comments
Alejandro
September 22, 2011
need some help with a query

I have a table "A" that have many rows with fields linking tables "B" and "C" this fields (called "A.b" and "A.c") are the rowids of tables "B" and "C" respectively. Inside tables "B" and "C" there are fields called "date-b" and "date-c" respectively. have that said...what I need is a list of "A.b" that mets the following conditions: "date-b" should be after an specific date, and "date-c" should be before an specific date... Any help whopuld be really appreciated.... Many thanks in advance
Paul
September 23, 2011
subqueries

thanks for this, i had been struggling all day to understand subqueries and given that mysql.com utterly useless this article helped me out alot. thank you.
veni
January 28, 2012
queries

nice
swapna
March 22, 2012
dbms

its very nice we can understand easily but to know the second max wt is the query is required ?
Agus Wahyudi
June 4, 2012
Subquery syntax error

Would you please correct an error #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '" + " ,mstr_customer cus" + " ,(' at line 1 on code : SELECT pj.faktur,pj.tanggal,cus.nama,fk.bayar" + " FROM ttrs_penjualan pj" + " ,mstr_customer cus" + " ,( SELECT sum( (prd.harga * pd.qty) - ((prd.harga * pd.qty) * (pd.diskon/100))) as bayar,pd.faktur" + " FROM ttrs_penjualan_detail pd" + " ,mstr_product prd" + " WHERE pd.prd_id=prd.prd_id" + " GROUP BY pd.faktur ) fk" + " WHERE pj.faktur=fk.faktur" + " AND pj.cust_id=cus.cust_id Thanx anyway.
Ajit
June 23, 2012
Subquries

thank u?
sampath
July 30, 2013
MYSQL

Inner Query for insert a record in one table after deletion that record in another table in mysql
DMCA.com