Joins Multiple Tables
Joins Multiple Tables CSC 271
Comsats Institute of Information Technology
Popular in Database Management
verified elite notetaker
Popular in ComputerScienence
This 31 page Class Notes was uploaded by Ismail Yousuf on Saturday January 9, 2016. The Class Notes belongs to CSC 271 at Comsats Institute of Information Technology taught by Amjad Usman in Winter 2016. Since its upload, it has received 18 views. For similar materials see Database Management in ComputerScienence at Comsats Institute of Information Technology.
Reviews for Joins Multiple Tables
Report this Material
What is Karma?
Karma is the currency of StudySoup.
You can buy or earn more Karma at anytime and redeem it for class notes, study guides, flashcards, and more!
Date Created: 01/09/16
CSC271 Database Systems Lecture 20 Displaying Data from Multiple Tables [SQL SELECT - JOIN] Instructor : Amjad Usman email@example.com Thursday, December 10, 2015 Objectives – Today’s Lecture Write multiple table SQL queries Understanding Joins Types of Joins 3 Introduction Querying one table already done & practiced! Real power of relational database Storage of data in multiple tables Necessitates creating queries to use multiple tables Two Basic approaches for processing multiple tables Sub-queries Join 4 Processing Multiple Tables Using Joins Join - Most frequently used operation - brings together data from multiple tables into one resultant table Join can be achieved in two ways Implicitlyby referring in aWHERE clause to the matching of common columns over which the tables are joined Explicitlyby JOIN…..ON commands in FROM clause 5 What is the Join? Use a join to query data from more than one table SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column1=table2.column2 Write the join condition in theWHERE clause Prefix the column name with the table name when the same column name appears in more than one table 6 Types of Joins Joins that are compliant with the SQL include the following: Equijoin / Inner Join Natural joins Self join Non-equijoin Outer join Cross Join 7 SQL Joins:Defining Join Types: INNER JOIN A B INTERSECTION 8 SQL Joins Defining Join Types: INNER JOIN An INNER JOIN is also an equijoin,or equality join between equals. An INNER JOIN matches on one or a set of columns values from one table: When one table is involved,an INNER JOIN creates an intersection between two copies of a single table (typically done with two different column names). When two or more tables are involved,an INNER JOIN creates an intersection between the tables based on designated column names. 9 Defining Join Types: INNER JOIN Create an INNER JOIN by placing a position specific set of tables in the FROM clause followed by an ON or USING clause. Equality statements are between one or more columns in two copies of one table or two tables: When the columns share the same name and data type, use the USING clause. When the columns have different names but the same data type, use the ON clause. If only the word JOIN is used,an INNER JOIN is assumed by the SQL parser. 10 Defining Join Types: INNER JOIN SELECT a.column1, b.column2 FROM table1 a, table2 b WHERE a.columnpk = b.columnfk; SELECT a.column1, b.column2 FROM table1 a [INNER] JOIN table2 b ON a.columnpk = b.columnfk; SELECT a.column1, b.column2 FROM table1 a [INNER] JOIN table2 b USING (same_column_name); 11 Cartesian Products 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. 12 Generating a Cartesian Product EMPLOYEES (20 rows) DEPARTMENTS (8 rows) … Cartesian product: 20 x 8 = 160 rows … 13 Creating Cross Joins The CROSS JOIN clause produces the cross-product of two tables. This is also called a Cartesian product between the two tables. SELECT last_name, department_name FROM employees CROSS JOIN departments ; … 14 Retrieving Record with Equijoin Employees ∞ Department DEPARTMENTS EMPLOYEES Foreign key Primary key 15 Using Equijoin Write SQL statement to do this: Employees ∞ Department Select * From employees ,departments Where employees.department_id=departments.department_id 16 Qualifying Ambiguous Column Names Use table prefixes to qualify column names that are in multiple tables. Use table prefixes to improve performance. Use column aliases to distinguish columns that have identical names but reside in different tables. 17 Using Table Aliases Use table aliases to simplify queries. Use table aliases to improve performance. SELECT e.employee_id, e.last_name, d.location_id, department_id FROM employees e INNER JOIN departments d USING (department_id) ; 18 Retrieving Records with the ON Clause SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id FROM employees e INNER JOIN departments d ON (e.department_id = d.department_id); … 19 Retrieving Records with the USING Clause SELECT employees.employee_id, employees.last_name, departments.location_id, department_id FROM employees INNER JOIN departments USING (department_id) ; … 20 SELECT s.sid, s.name, r.bid FROM Sailors s INNER JOIN Reserves r ON s.sid = r.sid sid sname rating age sid bid day 22 Dustin 7 45.0 22 101 10/10/96 31 Lubber 8 55.5 95 103 11/12/96 95 Bob 3 63.5 s.sid s.name r.bid 22 Dustin 101 95 Bob 103 21 Joins Example Show all customers and order date who have placed an order SELECT CUSTOMER_NAME ,ORDER_DATE FROM CUSTOMER,ORDER WHERE CUSTOMER.CUSTOMER_ID = ORDER.CUSTOMER_ID SELECT CUSTOMER_NAME ,ORDER_DATE FROM CUSTOMER INNER JOIN ORDER ON CUSTOMER.CUSTOMER_ID = ORDER.CUSTOMER_ID SELECT CUSTOMER_NAME ,ORDER_DATE FROM CUSTOMER INNER JOIN ORDER USING CUSTOMER_ID 22 Applying Additional Conditions to a Join Show employee id ,last name,dept id and location id who have a manager ID 149. SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id FROM employees e INNER JOIN departments d ON (e.department_id = d.department_id) AND e.manager_id = 149 ; 23 Joins Example Show the students’ name and marks who failed in course CSC271 SELECT S.std_name, R.marks FROM Student S INNER JOIN Result R ON S.std_id = R.std_id AND R.marks<50 AND course_id = ‘CSC271’ SELECT S.std_name, R.marks FROM Student S INNER JOIN Result R USING std_id AND R.marks<50 AND course_id = ‘CSC271’ 24 Joining More than two table Employees Departments Locations 25 Joining More than two table select first_name,department_name,city from employees E,departments D,locations L where E.department_id=D.department_id and D.location_id=L.location_id select first_name,department_name,city from employees JOIN departments ON(employees.department_id=departments.department_id) JOIN locations ON(departments.location_id=locations.location_id) select first_name,department_name,city from employees JOIN departments using(department_id) JOIN locations using(location_id) 26 SQL Joins Defining Join Types: Non-equijoin A non-equijoin is an indirect match: Occurs when one column value is found in the range between two other column values Uses the BETWEEN operator. Also occurs when one column value is found by matching against a criterion using an inequality operator. 27 SQL Joins Defining Join Types: Non-equijoin Example: SELECT a.column1, b.column2 FROM table1 a, table2 b WHERE a.columnpk >= b.columnfk; SELECT a.column1, b.column2 FROM table1 a, table2 b WHERE a.cola BETWEEN b.colx AND b.coly; 28 Non-Equijoins EMPLOYEES JOB_GRADES Salary in the EMPLOYEES table must be between lowest salary and highest … salary in the JOB_GRADES table. 29 Retrieving Records with Non-Equijoins SELECT e.last_name, e.salary, j.grade_level FROM employees e JOIN job_grades j ON e.salary BETWEEN j.lowest_sal AND j.highest_sal; … 30 THANK YOU SO MUCH 31 COMSATS Institute of Information Technology, Wah Campus
Are you sure you want to buy this material for
You're already Subscribed!
Looks like you've already subscribed to StudySoup, you won't need to purchase another subscription to get this material. To access this material simply click 'View Full Document'