mySQL subquery

a subquery is a query that is nested inside a select, insert, update, or delete statement, or inside another subquery.a subquery also must be enclosed in parentheses.mysql subqueries can return a single row,single column, single value, or can be return a table.

look a simple example:

  • the subquery returns all offices codes of the offices that locate in the usa.
  • the outer query selects the last name of employees whose office code is in the     result-set returned from the subquery.


types of subqueries

subqueries can be used in a number of different ways:

  • within a where or having clause
  • with comparison and logical operators
  • with the in or not in membership test
  • with the exists or not exists boolean test
  • with any, some or all
  • within a from clause
  • with joins
  • with update, insert and delete queries

below is selection from the "users" table:

useridfirstnamelastnamemobilenocity
1sandeepkumar9549293245india
2ramkumar9549293212usa
3shyamdhaka2323234545jaipur
4ankitnehra3423567845uae
5radhakumari2323121212jaipur

and selection from the "company" table:

productidcompanynametype
1facebooksocial website
2gmailmailing service
3googlesearch engine

mySQL subquery within a WHERE clause

you can use comparison operators to compare it with the expression in the where clause.

example

     select   firstname, city

                    from     users 
                    
                    where    userid = (select   productid   from   company);

output : try it youself


example :  mysql subquery with in operators

     select     firstname, lastname

                    from      users 
                    
                    where    userid   in  (select   productid   from   company);

mySQL subquery in the  from  clause

subqueries in the from clause can't be correlated subqueries

syntax:

         select    ...    from     (subquery)         as     name ...