subqery którego nie da się osiągnąć przez join

SELECT * FROM t1
  WHERE column1 = (SELECT MAX(column2) FROM t2);

It finds all rows in table t1 containing a value that 
occurs twice in a given column
 
SELECT * FROM t1 AS t
  WHERE 2 = (SELECT COUNT(*) FROM t1 WHERE t1.id = t.id);



SELECT s1 FROM t1 WHERE s1 > ANY (SELECT s1 FROM t2);

IN === = ANY
SELECT s1 FROM t1 WHERE s1 = ANY (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 IN (SELECT s1 FROM t2);

ANY === SOME
SELECT s1 FROM t1 WHERE s1 <> ANY  (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 <> SOME (SELECT s1 FROM t2);

NOT IN === <> ALL
SELECT s1 FROM t1 WHERE s1 <> ALL (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 NOT IN (SELECT s1 FROM t2);




SELECT * FROM t1
  WHERE (col1,col2) = (SELECT col3, col4 FROM t2 WHERE id = 10);
SELECT * FROM t1
  WHERE ROW(col1,col2) = (SELECT col3, col4 FROM t2 WHERE id = 10);
  
  



średnia po sumach np. średnia po podsumowaniach faktur.

SELECT AVG(sum_column1)
  FROM (SELECT SUM(column1) AS sum_column1 FROM t1 GROUP BY column1) AS t1;
  
  
subqeries as joins
  
SELECT * FROM t1 WHERE id IN (SELECT id FROM t2);
SELECT DISTINCT t1.* FROM t1, t2 WHERE t1.id=t2.id;