Subqueries

ANY (SOME) and ALL

ANY - IF One Value from Sub Query is TRUE then Every Value is Returned

ALL - IF One Value from Sub Query is TRUE then Only That Value is Returned

SOME = ANY

SELECT colName
FROM tableName
WHERE colName1 > ANY
	(SELECT colNameForAny
	FROM tableName);

IN and NOT IN

IN - Return if Column is IN ()

NOT IN - do NOT Return if Column is IN ()

SELECT colName
FROM tableName
WHERE colName1 IN 
	('value', 'thing', 'tingige');

SELECT colName FROM tableName
WHERE colName1 IN (SELECT * FROM tableName1);

EXISTS

EXISTS - Returns the All the Rows that meets the requirement ()

SELECT colName
FROM tableName1
WHERE EXISTS
	(SELECT *
	FROM tableName2
	WHERE tableName2.colName2 = tableName1.colName2);

CHECK

CREATE TABLE Person (
name VARCHAR(10),
gender CHAR(1)  DEFAULT ‘U’ 
       CHECK (gender IN (‘M’,’F’,’U’))); # only entered if in the list