When working in a normalized environment, chances are one will have to combine t
ID: 3758964 • Letter: W
Question
When working in a normalized environment, chances are one will have to combine tables and get a result set into a table. To accomplish this task, the clause JOIN is used. Depending on what result is needed, different forms of this clause are used. They are:
INNER JOIN
OUTER JOIN (both LEFT and RIGHT)
FULL JOIN
CROSS JOIN
What all types of JOIN have in common is that they, based on a condition, match one record from one table to one or more records in another table. The result will be records that combine the data from both tables.
INNER JOINs are the most used type of JOIN. They return only the data for which matches were found.
SELECT *
FROM Person.Person
INNER JOIN HumanResources.Employee
ON Person.Person.BusinessEntityID =
HumanResources.Employee.BusinessEntityID
Using the example above, write an SQL query that returns all the information for all contacts stored in the Person.BusinessEntity table and only the jobTitle from the HumanResources.Employee table. Note that multiple rows may be returned.
Submit the SQL statement used to accomplish this task.
Explanation / Answer
The SQL Joins clause is used to combine records from two or more tables in a database. A JOIN is a means for combining fields from two tables by using values common to each.
There are different types of joins available in SQL:
INNER JOIN: returns rows when there is a match in both tables.
LEFT JOIN: returns all rows from the left table, even if there are no matches in the right table.
RIGHT JOIN: returns all rows from the right table, even if there are no matches in the left table.
FULL JOIN: returns rows when there is a match in one of the tables.
SELF JOIN: is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.
CARTESIAN JOIN: returns the Cartesian product of the sets of records from the two or more joined tables.
The SQL Joins clause is used to combine records from two or more tables in a database. A JOIN is a means for combining fields from two tables by using values common to each.
There are different types of joins available in SQL:
INNER JOIN: returns rows when there is a match in both tables.
LEFT JOIN: returns all rows from the left table, even if there are no matches in the right table.
RIGHT JOIN: returns all rows from the right table, even if there are no matches in the left table.
FULL JOIN: returns rows when there is a match in one of the tables.
SELF JOIN: is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.
CARTESIAN JOIN: returns the Cartesian product of the sets of records from the two or more joined tables.
Inner Join
The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns in both tables.
exp.
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;
Outer JOin
Outer Join is based on both matched and unmatched data. Outer Joins subdivide further into,
Left Outer Join
Right Outer Join
Full Outer Join
Left Outer Join
The left outer join returns a result table with the matched data of two tables then remaining rows of the left table and null for the right table's column.
exp.
SELECT * FROM class LEFT OUTER JOIN class_info ON (class.id=class_info.id);
Right Outer Join
The right outer join returns a result table with the matched data of two tables then remaining rows of the right table and null for the left table's columns.
SELECT * FROM class RIGHT OUTER JOIN class_info on (class.id=class_info.id);
Full Outer Join
The full outer join returns a result table with the matched data of two table then remaining rows of both left table and then the right table.
SELECT * FROM class FULL OUTER JOIN class_info on (class.id=class_info.id);
Cross Join
he CARTESIAN JOIN or CROSS JOIN returns the Cartesian product of the sets of records from the two or more joined tables. Thus, it equates to an inner join where the join-condition always evaluates to True or where the join-condition is absent from the statement.
exp.
SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS, ORDERS;
write an SQL query that returns all the information for all contacts stored in the Person.BusinessEntity table and only the jobTitle from the HumanResources.Employee table. Note that multiple rows may be returned.
SELECT *
FROM Person.Person
LEFT OUTER JOIN HumanResources.Employee
ON Person.Person.BusinessEntityID =
HumanResources.Employee.BusinessEntityID
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.