Lecture 5 SQL Structured Query Language MultiTable Queries

Lecture 5 SQL )Structured Query Language(

Multi-Table Queries (JOIN) �INNER JOIN �OUTER JOIN �LEFT OUTER JOIN �RIGHT OUTER JION �FULL OUTER JION

การใชงาน JOIN SELECT { column_name } [, . . . n] FROM { table_name } [, . . . n] [ [ INNER ] | { LEFT | RIGHT | FULL} [ OUTER ] ] JOIN { table_name } ON { search_conditions}

Example: INNER JOIN แบบท SELECT FROM ON แบบท 1 : ANSI SQL-92 Syntax b. *, p. * Branch 1 AS b INNER JOIN Property. For. Rent 1 AS p b. b. City = p. p. City; 2 : Transact - SQL SELECT b. *, p. * FROM Branch 1 AS b, Property. For. Rent 1 AS p WHERE b. b. City = p. p. City;

Example: INNER JOIN

Example: INNER JOIN

Example: LEFT OUTER JOIN แบบท 1 : ANSI SQL-92 Syntax SELECT FROM ON b. *, p. * Branch 1 AS b LEFT JOIN Property. For. Rent 1 AS p b. b. City = p. p. City; แบบท 2 : Transact - SQL SELECT b. *, p. * FROM Branch 1 AS b, Property. For. Rent 1 AS p WHERE b. b. City *= p. p. City;

Example: LEFT OUTER JOIN

Example: RIGHT OUTER JOIN แบบท 1 : ANSI SQL-92 Syntax SELECT FROM ON b. *, p. * Branch 1 AS b RIGHT JOIN Property. For. Rent 1 AS p b. b. City = p. p. City; แบบท 2 : Transact - SQL SELECT b. *, p. * FROM Branch 1 AS b, Property. For. Rent 1 AS p WHERE b. b. City =* p. p. City;

Example: RIGHT OUTER JOIN

Example: FULL OUTER JOIN SELECT FROM ON b. *, p. * Branch 1 AS b FULL JOIN Property. For. Rent 1 AS p b. b. City = p. p. City; (SELECT b. *, p. * FROM Branch 1 AS b LEFT JOIN Property. For. Rent 1 AS p ON b. b. City = p. p. City) UNION (SELECT b. *, p. * FROM Branch 1 AS b RIGHT JOIN Property. For. Rent 1 AS p ON b. b. City = p. p. City);

Example: FULL OUTER JOIN

Example: Three Table JOIN แบบท 2 : Transact - SQL SELECT b. branch. No, b. city, e. emp. No, e. emp. Name, p. property. No, p. street FROM Branch AS b, Employee AS e, Property_For_Rent AS p WHERE b. branch. No = e. branch. No AND e. emp. No = p. emp. No

Example: Three Table JOIN