SQL Programming (Sub-query)

Comparison using Sub-query

SELECT * FROM t1
  WHERE col1 = (SELECT MAX(col2) FROM t2);

SELECT * FROM t1 AS t
  WHERE 2 < (SELECT COUNT(*) FROM t1 WHERE t1.v1 = t.v1);
SELECT * FROM t1
  WHERE (col1,col2) = (SELECT col3, col4 FROM t2);

The subquery cannot return more than 1 row

Operator: =, <>, <,>, <=

Subqueries with ANY, IN, ALL or SOME

SELECT v1 FROM t1 WHERE v1 > ANY (SELECT v2 FROM t2);

SELECT v1 FROM t1 WHERE v1 IN (SELECT v2 FROM t2);

SELECT v1 FROM t1 WHERE v1 NOT IN (SELECT v2 FROM t2);

SELECT v1 FROM t1 WHERE v1 > ALL (SELECT v2 FROM t2);

SELECT col1,col2
  FROM t1
  WHERE (col1,col2) IN
         (SELECT col1,col2 FROM t2);

Exist & No Exist

SELECT DISTINCT v1 FROM t1
  WHERE EXISTS (SELECT * FROM t2
                WHERE t1.v1 = t2.v1);

SELECT DISTINCT v1 FROM t1
  WHERE NOT EXISTS (SELECT * FROM t2
                WHERE t1.v1 = t2.v1);

Co-related Sub-query

SELECT * FROM t1
  WHERE col1 = ANY (SELECT col1 FROM t2
                       WHERE t2.col2 = t1.col2);

Subquery in the FROM Clause

SELECT v1, v2
  FROM (SELECT a AS v1, b AS v2 FROM t1) AS t
  WHERE v1 > 1;

Subquery as Expression

SELECT SELECT max(id) FROM t, ...

Update with Sub-query

UPDATE tb1 AS t1
SET t.v1 =
 (SELECT MAX(t2.v)
  FROM tb2 AS t2
  WHERE t2.id = t1.id);

Delete with Sub-query

DELETE FROM tb1
WHERE NOT EXISTS (SELECT 1
  FROM tb2
  WHERE tb2.id = tb1.id);

Remove redundant rows

DELETE FROM t1
WHERE rowid not in
(SELECT MIN(rowid)
FROM t1
GROUP BY col1, col2, col3) ;

Other Sub-query

Select max(salary) from emp where salary
not in (select max(salary) from emp)
SELECT * FROM tbl1 ORDER BY col1 DESC LIMIT (1, 1)

Note

  • It it illegal to update and select from the same table in a subquery: