Joins

Chapter 14:

Joins: insert responsible teacher for each student  and join students and teachers name and display the students responsible teachers name and phone number.

1. What is joins
2. Why joins are important
3. Without joins?
4. Types of Joins

Create Supplier:



Create Customer:



Create Product:



Create Orders:



Create OrderItem:

Join: Join is used to combine rows and retrieve data from multiple tables.

Why Joins are important?
In SQL, joins are very important. Data is stored in database in the form of tables. All the data is stored in one table is not efficient manner, so data is stored in different tables. Whenever get back the data from multiple tables joins are required.

Without Joins?
By using sub query we can get the data from multiple tables, but processor get burden to process sub queries.

Types of Joins:
 1. Inner Join
 2. Left Outer Join
 3. Right Outer Join
 4. Full Outer Join
 5. Self Join




1.Inner Join: The Inner Join keyword select records that have matching values in both tables.

2. Left Outer Join: The Left Outer Join keyword returns all records from the left table, and the matched records from the right table. The result is Null from the right side, if there is no match.

3. Right Outer Join: The Right Outer Join keyword returns all records from the right table, and the matched records from the left table. The result is Null from left side, if there is no match.






4. Full Outer Join: The Full Outer Join keyword returns all records when there is a match in either left or right table records. Full Outer Join can potentially return very large result-sets.

5. Self Join: A Self Join is a regular join, but the table is joined with itself. It is useful for querying hierarchical data or comparing rows within the same table. A self join uses the inner join or left join clause. Because the query that uses self join references the same table, the table alias is used to assign different names to the table within the query.







Union: Union operator is used to combine the result sets of 2 or more select statements. It removes duplicate rows between the various select statements. Each select statement within the union operator must have the same number of columns in the result sets with similar data types.






Union All: Union All is similar to Union but here duplicate records are allowed in Union All.






Group By: 

Create a table for Departments in your college like.. ECE, EEE,IT,CSE..

And in teachers table assign the teachers to departments and get the departments wise teachers count

1. Group by single field
2. Group by Multiple field
3. Group with having

Create and insert Departments table:

Create and insert Teacher table:

Group by: single field

Group by: multiple fields

Group with Having: