Monday 13 January 2014

mysqlsubqueries examples Scalar Subqueries Row Subqueries Column Subqueries Table Subqueries

mysqlsubqueries examples Scalar Subqueries Row Subqueries Column Subqueries Table Subqueries

It is query very similar to Normal queries in databases like
It may return one column
it may return more column
It may return single rows
It may return multiple rows

OR
any of above combination.

SubQuries are those queries which are used within Normal Queries.


For Example
select * from users where user_id =(select user_id from profile where type='user');
Here queries used within bracket is known as sub queris

There are four types of sub-queries.
a. Scalar Subqueries: return single columan of single row
select * from users where user_id =(select user_id from profile where type='user' limit 1);

b. Row Subqueries: Return single row
select u1.* from users As u1 left join (select user_id,country from profile limit 1) as u2 on u2.user_id=u1.user_id and u2.country=u1.country

c. Column Subqueries: Return one column of one or more rows
select * from users where user_id in(select user_id from profile where type='user');

d. Table Subqueries: return one more col of one/more rows
select u1.* from users As u1 left join (select user_id,country from profile) as u2 on u2.user_id=u1.user_id and u2.country=u1.country