8-Join Statement.pdf

JOIN Statements Objectives Understand the syntax and structure of different types of JOIN statements in MySQL. Use INNER JOIN to retrieve matching records from two or more tables. Implement LEFT JOIN and RIGHT JOIN to retrieve unmatched records from one side of the join. Simulate FULL...

JOIN Statements Objectives Understand the syntax and structure of different types of JOIN statements in MySQL. Use INNER JOIN to retrieve matching records from two or more tables. Implement LEFT JOIN and RIGHT JOIN to retrieve unmatched records from one side of the join. Simulate FULL OUTER JOIN using UNION in MySQL. Discuss the role of primary and foreign keys to establish relationships between tables during joins. What is JOIN? A JOIN clause is used to combine rows from two or more tables, based on a related column between them. It is a means of combining data in fields from two tables by using values common to each table. Types of JOIN 1. Inner Join 2. Left Join 3. Right Join 4. Full Outer Join What is MySQL Inner Join? The INNER JOIN keyword selects records that have matching values in both tables. Sample Table Orders Table Customers Table MySQL Inner Join Example SELECT Orders.OrderID, Customers.CustomerName FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID; Note: The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns. If there are records in the "Orders" table that do not have matches in "Customers", these orders will not be shown. MySQL Inner Join Example SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName FROM ((Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID) INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID);  MySQL Left Join The LEFT JOIN keyword returns all records from the left table (table1), and the matching records (if any) from the right table (table2). MySQL Right Join The RIGHT JOIN keyword returns all records from the right table (table2), and the matching records (if any) from the left table (table1). Full Outer Join Full JOINs combine both left and right joins by returning all rows from both tables, as long as there is at least one match between them. Full Outer Join Syntax SELECT * FROM table1 FULL OUTER JOIN table2 ON table1.id = table2.user_id Outer Join vs. Cross Join A CROSS JOIN produces a cartesian product between the two tables, returning all possible combinations of all rows. It has no ON clause because you're just joining everything to everything. A FULL OUTER JOIN is a combination of a LEFT OUTER and RIGHT OUTER JOIN. It returns all rows in both tables that match the query's WHERE clause, and in cases where the ON condition cannot be satisfied for those rows it puts NULL values for the unpopulated fields. Key Notes 1. The four most common join types are: Inner Join Left Join Right Join Full Outer Join 2. To join data, you need to establish the fields or fields used to link the tables together. 3. The syntax for executing each type of join is similar. The Role of Primary Keys and Foreign Keys Primary Keys A primary key is the column or columns containing values that uniquely identify each row in a table. Primary Keys Rules in Creating Primary Key 1. Must contain unique values 2. Cannot contain NULL values 3. A table can only have 1 primary key Foreign Keys A foreign key is a column or columns in a database that (e.g. table_1.column_a) that are linked to a column in a different table (table_2.column_b). The existence of a foreign key column establishes a foreign key constraint – a database rule that ensures that a value can be added or updated in column_a only if the same value already exists in column_b. Primary and Foreign Keys Primary and Foreign Keys A table can have more than 1 foreign key where each foreign key references to a primary key of different parent tables. Key Notes A primary key is a column or multiple columns in a table that enables each row to be uniquely identified A foreign key is a column or columns in a table that provides a link between data in two tables A primary key can be joined to the foreign key in a join condition A primary key must be unique and cannot contain NULL values A foreign key does not need to be unique and can contain NULL values. Summary of Key Roles Primary key ensures that each record in a table is unique, enabling accurate and consistent joins. Foreign key links tables together, facilitating relational database queries and enforcing referential integrity. During joins, the foreign key in one table is usually matched with the primary key in the other table to combine related rows across multiple tables. Without proper primary and foreign keys, joins would be less effective, and data integrity could be compromised. What is data integrity in database? Data integrity in a database refers to the accuracy, consistency, and reliability of the data stored. It ensures that the data remains correct and unchanged during operations such as inserts, updates, and deletions, and across multiple transactions or database operations. Joining Tables Overview of the Database Database Name: Shop Tables: employees, salary Overview of the Database Database Name: Shop Tables: employees, salary Overview of the Database Table name: employees Table name: salary  Check the data types of your columns before performing queries. Steps of Joining Tables 1. Identify the tables you would like to join (you may provide aliases for simplicity). 2. Establish the type of join required. 3. Establish joining condition. 4. Choose columns of data you want in your single result. Inner Join Overview of the Database Table name: employees Table name: salary MySQL Inner Join Syntax SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name; Example 1 Obtain employee ID, first name & last name, from the employee table and current salary from the salary table. Example 2 Obtain employee ID, first name & last name, from the employee table and prior and current salary from the salary table. Example 3 Obtain first name & last name, from the employee table and prior and current salary from the salary table. Example 4 What if you did not specify the employee id in a specific column? Error Code: 1052. Column 'emp_id' in field list is ambiguous Left Join Overview of the Database Table name: employees Table name: salary MySQL Left Join Syntax SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name; Left Join Example: Obtain all information from employees table and join salary table to bring back prior and current salary. Key Notes 1. Use the LEFT JOIN operator syntax to join tables together – all information in the left table will feed the result. 2. Information from the right table will be populated where there is a math in the join condition 3. Where there is a no match, the right table values will be poplulated with null. Right Join MySQL Right Join Syntax SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name; Overview of the Database Table name: employees Table name: salary Example-Right Join Obtain all information from the salary table and join to the employees table to bring back the employees first name Key Notes Right Table Join Example SELECT * FROM customer RIGHT JOIN salary ON customer.id = salary.customer_id; The above query will return all columns from the left and right tables. The rows from the right table will be present and populated with values from the left table where there is a match in the id from the customer table and the customer_id from the salary table. Where there is no match, the left table columns will be populated with null. Full Join Overview of the Database Table name: employees Table name: salary Full Outer Join Syntax MySQL does not directly support FULL OUTER JOIN, but you can simulate it using a combination of LEFT JOIN and RIGHT JOIN with a UNION, like this: SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id UNION SELECT * FROM table1 RIGHT JOIN table2 ON table1.id = table2.id; Example-Full Outer Join Example Full Outer Join with Redundancy