sql04 - 4 4 Displaying Data from Multiple Tables 4-2...

Info iconThis preview shows pages 1–10. Sign up to view the full content.

View Full Document Right Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: 4 4 Displaying Data from Multiple Tables 4-2 Objectives After completing this lesson, you should After completing this lesson, you should be able to do the following: be able to do the following: Write SELECT statements to access data from more than one table using equality and nonequality joins View data that generally does not meet a join condition by using outer joins Join a table to itself 4-3 EMPNO DEPTNO LOC----- ------- -------- 7839 10 NEW YORK 7698 30 CHICAGO 7782 10 NEW YORK 7566 20 DALLAS 7654 30 CHICAGO 7499 30 CHICAGO ... 14 rows selected. Obtaining Data from Multiple Tables EMP EMP DEPT DEPT EMPNO ENAME ... DEPTNO------ -----... ------7839 KING ... 10 7698 BLAKE ... 30 ... 7934 MILLER . .. 10 DEPTNO DNAME LOC ------ ---------- --------10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 4-4 What Is a Join? Use a join to query data from more than Use a join to query data from more than one table. one table. Write the join condition in the WHERE clause. Prefix the column name with the table name when the same column name appears in more than one table. SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column1 = table2.column2 ; 4-5 Cartesian Product A Cartesian product is formed when: A join condition is omitted A join condition is invalid All rows in the first table are joined to all rows in the second table To avoid a Cartesian product, always include a valid join condition in a WHERE clause. 4-6 Generating a Cartesian Product ENAME DNAME------ ----------KING ACCOUNTING BLAKE ACCOUNTING ... KING RESEARCH BLAKE RESEARCH ... 56 rows selected. EMP (14 rows) EMP (14 rows) DEPT (4 rows) DEPT (4 rows) EMPNO ENAME ... DEPTNO------ -----... ------7839 KING ... 10 7698 BLAKE ... 30 ... 7934 MILLER . .. 10 DEPTNO DNAME LOC ------ ---------- --------10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON Cartesian Cartesian product: product: 14*4=56 rows 14*4=56 rows 4-7 Types of Joins Oracle Proprietary Oracle Proprietary Joins (8i and prior) Joins (8i and prior) Eqijoin Eqijoin Non-equijoin Non-equijoin Outer join Outer join Self join Self join SQL:1999 Compliant SQL:1999 Compliant joins joins Cross join Cross join Natural joins Natural joins Using clauses Using clauses Full or two sided outer Full or two sided outer joins joins Arbitrary join Arbitrary join condition for outer condition for outer joins joins 4-8 Types of Joins Equijoin Non-equijoin Outer join Self join 4-9 What Is an Equijoin?...
View Full Document

This note was uploaded on 07/15/2011 for the course ECO 2023 taught by Professor Mr.raza during the Summer '10 term at FAU.

Page1 / 37

sql04 - 4 4 Displaying Data from Multiple Tables 4-2...

This preview shows document pages 1 - 10. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online