การ Join บน SQL และการใช้ Sub Query เพื่อพัฒนา Skill การเขียน Query ขั้นเทพ!!!

SELECT t1.*,t2.* FROM table1 t1
  INNER JOIN (SELECT * FROM table2) t2

SELECT column
FROM table1
WHERE column IN (SELECT column
                   FROM table2 
                  WHERE condition)

SELECT column = (SELECT column FROM table2 WHERE condition), column2, ....
  FROM table1
 WEHRE condition

 UPDATE table2 SET column =  (SELECT column FROM table2 WHERE condition)
 WEHRE condition

 SELECT t1.Column1, t1.Column2, 
       ColumnNew = (SELECT COUNT(t2.Column2) FROM table2 t2 WHERE t2.Colum1 = t1.Colum1)
  FROM table1 t1

SELECT CountryCode, MAX(Budget) AS Budget 
	FROM customer  GROUP BY CountryCode

SELECT c1.CustomerID, c1.Name, c2.CountryCode, c2.Budget FROM customer c1
INNER JOIN 
	(SELECT CountryCode, MAX(Budget) AS Budget 
		    FROM customer  GROUP BY CountryCode) c2 ON  c1.CountryCode = c2.CountryCode 
			AND c1.Budget = c2.Budget

SELECT c3.CountryCode, c4.CountryName, c3.Budget
	FROM country c4
	INNER JOIN 
		(SELECT c1.CustomerID, c1.Name, c2.CountryCode, c2.Budget FROM customer c1
		INNER JOIN 
			(SELECT CountryCode, MAX(Budget) AS Budget 
				    FROM customer  GROUP BY CountryCode) c2 ON  c1.CountryCode = c2.CountryCode 
					AND c1.Budget = c2.Budget)	
					    c3 ON c4.CountryCode = c3.CountryCode;

SELECT CustomerID, COUNT(AuditID) AS NumCount 
	FROM audit GROUP BY CustomerID

SELECT MAX(t1.NumCount) AS MaxAudit, MIN(NumCount) AS MinAudit FROM 
	(SELECT CustomerID, COUNT(AuditID) AS NumCount 
		FROM audit GROUP BY CustomerID) t1