1 / 7

JOINs

JOINs. Selecting from two or more tables. Introduction to JOIN. Normalization produces lots of (small) tables Using JOIN we can extract data from many tables in a single SELECT statement Different kinds of JOINs INNER JOIN OUTER JOIN (LEFT and RIGHT) FULL JOIN CROSS JOIN UNION.

walter
Download Presentation

JOINs

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. JOINs Selecting from two or more tables Select with JOINs

  2. Introduction to JOIN • Normalization produces lots of (small) tables • Using JOIN we can extract data from many tables in a single SELECT statement • Different kinds of JOINs • INNER JOIN • OUTER JOIN (LEFT and RIGHT) • FULL JOIN • CROSS JOIN • UNION Select with JOINs

  3. INNER JOIN • INNER JOIN combines two (or more) tables • Usually connected through a primary key – foreign key relationship • Syntax • SELECT … FROM tableA INNER JOIN tableB on tableA.attrib = tableB.attrib • The word INNER might be omitted • Combines all rows of tableA with all rows of tableB where attributes are equal • If a row in tableA does not have a matching row in tableB, that row is NOT included in the result • And vice versa • Alternative (old) syntax • SELECT … FROM tableA, tableB WHERE tableA.attrib = tableB.attrib Select with JOINs

  4. OUTER JOINs • Sometimes we want rows without matching rows included in the result • Syntax • SELECT … FROM tableA LEFT OUTER JOIN tableB ON tableA.attrib = tableB.attrib • Rows from tableA without matching rows in tableB, will be included in the result. • RIGHT OUTER JOIN • Vice versa Select with JOINs

  5. FULL JOIN • LEFT + RIGHT OUTER JOIN • Syntax • SELECT … FROM tableA FULL OUTER JOIN tableB ON tableA.attrib = tableB.attrib • Rows from tableA and tableB are included in the result, no matter if they have a matching row in the other table • Not used very often Select with JOINs

  6. CROSS JOIN • Syntax • SELECT … FROM tableA CROSS JOIN tableB • Combines all rows of tableA with all rows of tableB • Called Cartesian product • Not used very often • Alternative syntax • SELECT … FROM tableA, tableB • No WHERE clause, no restrictions on output! Select with JOINs

  7. UNION • Combines the results of two SELECTs • Syntax • SELECT … UNION SELECT … • The two result must be UNION compatible • Same number of columns • Data type of attributes must be compatible • For each column • Not used very often Select with JOINs

More Related