JOIN

• JOIN


  • Staff_ID
  • Staff_Name
  • Job_ID
• Modules


  • CSC1023, Databases, 1, 1
  • CSC1029, Object Oriented Programming, 2, 2
  • CSC1030, Web Technologies, 3, 3
  • CSC1031, Software Design Principles, 3, 4
• Students


  • 40319000, CSC1023, Lee Chong Wei, 123 3rd Street, Los Angelas, CA
  • 40319001, CSC1023, Victor Axelson, 123 4th Street, New York, NY
  • 40319002, CSC1029, Low Kean Yew, 123 6th Street, Milwaukee, WI
  • 40319003, CSC1030, John Dory, 789 29th Street, Denver, CO
  • 40319004, CSC1031, Harry Ramsdens, 789 26th Street, Miami, FL
• Relational Example, Dr Bee Yen Toh, Queen's University Belfast, CSC1023 - Databases
• Job_details


  • 1, Research Fellow
  • 2, Leturer
  • 3, Senior Lecturer
  • 4, Reader
  • 5, Professor
• Assessments


  • 1, Class Test, Online, wk 7
  • 2, Project, hard-copy, wk 12
  • 3, Exam, Online, wk 13
• A Join is a query that combines data from multiple tables, based on a related attribute between the tables.
• Types of JOIN


  • INNER JOIN
  • LEFT JOIN
  • Self JOIN
  • RIGHT JOIN
  • CROSS JOIN
• INNER JOIN


  Retrieves rows that have matching values in 2 or more tables. Used when you only want to see the records where there is a match in the joined tables.
• Self JOIN


  Join a table to itself, for comparing rows within the same table.
• LEFT JOIN


  All Rows from Left table + matching rows from Right table
• RIGHT JOIN


  All Rows from Right table + matching rows from Left table
• CROSS JOIN


  Combines each row of one table with each row of another table, resulting in the Cartesian product of the sets.
• Attribute names don't have to be identical, but there must be a matching value (CSC1023)
• If the attribute name in both tables are the same, you can use USING(Module_ID) instead of ON Students.Module_ID = Modules.courseID
• SELECT Student_Name, Title FROM Students INNER JOIN Modules ON Students.Module_ID = Modules.Module_ID ;

  
• SELECT Student_Name, Title FROM Students INNER JOIN Modules USING (Module_ID) ;
• INNER JOIN - different Column names
• ALTER TABLE Modules CHANGE Module_ID Course_ID VARCHAR(20);
• SELECT Student_Name, Title FROM Students INNER JOIN Modules ON Students.Module_ID = Modules.Course_ID ;

  
• SELECT Student_Name, Title FROM Students INNER JOIN Modules ON Students.Module_ID = Modules.Module_ID WHERE Students.module_ID = 'CSC1023' ;

  
• SELECT DISTINCT Student_Name, Title FROM Students INNER JOIN Modules ON course_ID = Module_ID WHERE course_ID = 'CSC1023' ORDER BY Student_Name DESC ;

  
• SELECT DISTINCT * FROM Students INNER JOIN Modules ON course_ID = Module_ID ORDER BY Student_ID DESC ;
• SELECT DISTINCT * FROM Students INNER JOIN Modules ON course_ID = Module_ID ORDER BY Student_ID DESC LIMIT 0,3 ;
• Example 2: Customer and Booking

  
• DROP TABLE IF EXISTS booking;
• CREATE TABLE `booking` ( `id` INT NOT NULL AUTO_INCREMENT, `customerid` INT NULL, `booking` DATETIME, PRIMARY KEY(`id`), FOREIGN KEY(`customerid`) REFERENCES `customer`(`id`) ON DELETE RESTRICT ON UPDATE RESTRICT );
• INSERT INTO `booking`(`customerid`, `booking`) VALUES (1,'2018-01-11 12:30:00'), (1,'2018-01-23 19:30:00'), (3,'2018-01-20 20:00:00'), (4,'2018-01-21 13:00:00');

  
• DROP TABLE IF EXISTS customer;
• CREATE TABLE `customer` ( `id` INT NOT NULL AUTO_INCREMENT , `name` VARCHAR(255) NOT NULL , PRIMARY KEY (`id`) );
• INSERT INTO customer (id, `name`) VALUES ('Dave'), ('Sarah'), ('Jim'), ('Alice');
• Get Bookings
• Get CustomerID
• Get Customer Name from Customer ID
• Display Results
• Qs: How are the tables arranged?
• SELECT * FROM booking INNER JOIN customer ON booking.customerid=customer.id

  
• SELECT name, booking FROM customer INNER JOIN booking ON booking.customerid=customer.id;

  
• LEFT JOIN Selects all records from the left table and matched records from the right table
• SELECT * FROM customer LEFT JOIN booking ON booking.customerid=customer.id;