CLASS 3 - Joins in SQL
1. What is SQL JOIN’s?
An SQL join clause combines columns from one or more tables in a relational database. It creates a set that can be saved as a table or used as it is. A JOIN is a means for combining columns from one (self-join) or more tables by using values common to each.
An SQL join clause combines columns from one or more tables in a relational database. It creates a set that can be saved as a table or used as it is. A JOIN is a means for combining columns from one (self-join) or more tables by using values common to each.
2. What are the different
types of SQL JOINs ?
The list of different types of SQL JOINs as follows:
The list of different types of SQL JOINs as follows:
·
INNER JOIN
·
LEFT JOIN
·
RIGHT JOIN
·
FULL JOIN
3. What is left outer join?
SQL LEFT JOIN Keyword. The LEFT JOIN keyword returns all records from the lefttable (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match.
SQL LEFT JOIN Keyword. The LEFT JOIN keyword returns all records from the lefttable (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match.
4. What is a full outer join?
SQL FULL OUTER JOIN Keyword. The FULL OUTER JOIN keyword return all records when there is a match in either left (table1) or right (table2) table records.
Note: FULL OUTER JOIN can potentially return very large result-sets!
SQL FULL OUTER JOIN Keyword. The FULL OUTER JOIN keyword return all records when there is a match in either left (table1) or right (table2) table records.
Note: FULL OUTER JOIN can potentially return very large result-sets!
5. Can you join a table to
itself in SQL?
Using Self-Joins. A table can be joined to itself in a self-join. Use a self-join when you want to create a result set that joins records in a table with other records in the same table. To list a table two times in the same query, you must provide a table alias for at least one of instance of the table name
Using Self-Joins. A table can be joined to itself in a self-join. Use a self-join when you want to create a result set that joins records in a table with other records in the same table. To list a table two times in the same query, you must provide a table alias for at least one of instance of the table name
6. What is Right Join in SQL?
Right join is used to retrieve rows which are common between the tables and all rows of Right hand side table. It returns all the rows from the right hand side table even though there are no matches in the left hand side table
Right join is used to retrieve rows which are common between the tables and all rows of Right hand side table. It returns all the rows from the right hand side table even though there are no matches in the left hand side table
7. What is Left Join in SQL?
Left join is used to retrieve rows which are common between the tables and all rows of Left hand side table. It returns all the rows from Left hand side table even though there are no matches in the Right hand side table.
Left join is used to retrieve rows which are common between the tables and all rows of Left hand side table. It returns all the rows from Left hand side table even though there are no matches in the Right hand side table.
8. What is Full Join in SQL?
Full join return rows when there are matching rows in any one of the tables. This means, it returns all the rows from the left hand side table and all the rows from the right hand side table.
Full join return rows when there are matching rows in any one of the tables. This means, it returns all the rows from the left hand side table and all the rows from the right hand side table.
9. What is Difference between
Inner Join and Outer Join?
Both inner join and outer join are the joining technique where matching records from the participating tables are displayed. That is when two tables are joined, based on the common column in both the tables, records are pulled.
In inner join method, when two tables are joined, only those records from both the tables are retrieved for which there exact match is based on the common column. This common column is based on the primary key of one table and is foreign key in other table. When there is exact match for these two column values records are retrieved.
Both inner join and outer join are the joining technique where matching records from the participating tables are displayed. That is when two tables are joined, based on the common column in both the tables, records are pulled.
In inner join method, when two tables are joined, only those records from both the tables are retrieved for which there exact match is based on the common column. This common column is based on the primary key of one table and is foreign key in other table. When there is exact match for these two column values records are retrieved.
10. What is a Self Join?
A self-join is simply a normal SQL join that joins one table to itself. Joining a table to itself can be useful when you want to compare values in a column to other values in the same column.
A self-join is simply a normal SQL join that joins one table to itself. Joining a table to itself can be useful when you want to compare values in a column to other values in the same column.
11. Is Self Join Inner Join or
Outer Join?
A self-join can be an inner join or an outer join or even a cross join. A table is joined to itself based upon a column that have duplicate data in different rows.
A self-join can be an inner join or an outer join or even a cross join. A table is joined to itself based upon a column that have duplicate data in different rows.
12. What is the difference
between LEFT JOIN and RIGHT JOIN?
LEFT JOIN and RIGHT JOIN actually both do
very similar things: they display the results of a JOIN query
including all records on a given table. The only difference is
that LEFT JOIN displays
all records on the left table of the query, and RIGHT JOIN displays all
records on the right table!
Different Types of SQL JOINs
Here are the different types of the JOINs in SQL:
- (INNER) JOIN: Returns records that have matching values in both tables
- LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table
- RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table
- FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table
SQL INNER JOIN Keyword
The INNER JOIN keyword selects records that have matching values in both tables.
INNER JOIN Syntax
SELECT column_name(s)
FROM table1
INNER JOIN table2ON table1.column_name = table2.column_name;
JOIN Three Tables
The following SQL statement selects all orders with customer and shipper information:
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);
SQL LEFT JOIN Keyword
The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match.
LEFT JOIN Syntax
SELECT column_name(s)
FROM table1
LEFT JOIN table2ON table1.column_name = table2.column_name;
Note: In some databases LEFT JOIN is called LEFT OUTER JOIN.
SQL LEFT JOIN Example
The following SQL statement will select all customers, and any orders they might have:
Example
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;
Note: The LEFT JOIN keyword returns all records from the left table (Customers), even if there are no matches in the right table (Orders).
SQL RIGHT JOIN Keyword
The RIGHT JOIN keyword returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side, when there is no match.
RIGHT JOIN Syntax
SELECT column_name(s)
FROM table1
RIGHT JOIN table2ON table1.column_name = table2.column_name;
Note: In some databases RIGHT JOIN is called RIGHT OUTER JOIN.
SQL RIGHT JOIN Example
The following SQL statement will return all employees, and any orders they might have placed:
Example
SELECT Orders.OrderID, Employees.LastName, Employees.FirstName
FROM Orders
RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY Orders.OrderID;
Note: The RIGHT JOIN keyword returns all records from the right table (Employees), even if there are no matches in the left table (Orders).
SQL FULL OUTER JOIN Keyword
The FULL OUTER JOIN keyword return all records when there is a match in left (table1) or right (table2) table records.
Note: FULL OUTER JOIN can potentially return very large result-sets!
Tip: FULL OUTER JOIN and FULL JOIN are the same.
FULL OUTER JOIN Syntax
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2ON table1.column_name = table2.column_nameWHERE condition;
SQL FULL OUTER JOIN Example
The following SQL statement selects all customers, and all orders:
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;
Note: The FULL OUTER JOIN keyword returns all matching records from both tables whether the other table matches or not. So, if there are rows in "Customers" that do not have matches in "Orders", or if there are rows in "Orders" that do not have matches in "Customers", those rows will be listed as well.
SQL Self JOIN
A self JOIN is a regular join, but the table is joined with itself.
Self JOIN Syntax
SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;
T1 and T2 are different table aliases for the same table.
SQL Self JOIN Example
The following SQL statement matches customers that are from the same city:
Example
SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.City
FROM Customers A, Customers B
WHERE A.CustomerID <> B.CustomerID
AND A.City = B.City
ORDER BY A.City;
Comments