Question

In: Computer Science

Can you please describe the different kinds of joins used in SQL?

Can you please describe the different kinds of joins used in SQL?

Solutions

Expert Solution

Different Types of SQL JOINs--------

(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 table2
ON table1.column_name = table2.column_name;

Example

SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

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 table2
ON table1.column_name = table2.column_name;

Example

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;

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 table2
ON table1.column_name = table2.column_name;

Example

SELECT Orders.OrderID, Employees.LastName, Employees.FirstName
FROM Orders
RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY Orders.OrderID;

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 table2
ON table1.column_name = table2.column_name
WHERE condition;

EXAMPLE

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;

Different Types of SQL JOINs--------

(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 table2
ON table1.column_name = table2.column_name;

Example

SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

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 table2
ON table1.column_name = table2.column_name;

Example

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;

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 table2
ON table1.column_name = table2.column_name;

Example

SELECT Orders.OrderID, Employees.LastName, Employees.FirstName
FROM Orders
RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY Orders.OrderID;

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 table2
ON table1.column_name = table2.column_name
WHERE condition;

EXAMPLE

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;

Different Types of SQL JOINs--------

(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 table2
ON table1.column_name = table2.column_name;

Example

SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

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 table2
ON table1.column_name = table2.column_name;

Example

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;

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 table2
ON table1.column_name = table2.column_name;

Example

SELECT Orders.OrderID, Employees.LastName, Employees.FirstName
FROM Orders
RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY Orders.OrderID;

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 table2
ON table1.column_name = table2.column_name
WHERE condition;

EXAMPLE

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;

venereology answered 9 months ago
Next > < Previous

Related Solutions

1. Can you please describe the different kinds of joins used in SQL? 2. What SQL...
1. Can you please describe the different kinds of joins used in SQL? 2. What SQL syntax would you use to ensure you are not returning duplicates in your data sets? 3. What SQL syntax would you use to change data in a table if you had write access to the table? Having write access means the data manipulation would occur in the table being pulled from. 4. What SQL syntax would you use to change data in a table...
Write a SQL statement which joins the parts table with the supplier table and lists the...
Write a SQL statement which joins the parts table with the supplier table and lists the part_name, supplier_name for all parts in the part table. The supplier_id column in the suppliers table is the primary key in the suppliers table, and this key has been exported to the parts table where it is a foreign key. You should use an inner join for this query. Write a SQL statement which joins the parts table with the suppliers table and lists...
Write a SQL statement which joins the parts table with the supplier table and lists the...
Write a SQL statement which joins the parts table with the supplier table and lists the part_name, supplier_name for all parts in the part table. The supplier_id column in the suppliers table is the primary key in the suppliers table, and this key has been exported to the parts table where it is a foreign key. You should use an inner join for this query. Write a SQL statement which joins the parts table with the suppliers table and lists...
Having learned different kinds of costs and the systems used to accumulate them, what kinds of...
Having learned different kinds of costs and the systems used to accumulate them, what kinds of costs would a social media company like Twitter or Tik-Tok, would incur? Are there labor, material, and overhead costs? Are there fixed and variable costs and direct and indirect costs? What are the types of services produced by social media companies? And how do the costs incurred relate to the services they generate? (300 words) C-5: As a cost accountant of a company that...
1. Write a SQL statement which joins the rider_student table with the rider_major table and lists...
1. Write a SQL statement which joins the rider_student table with the rider_major table and lists the rider student name and the name of the major (major_name) and the description of the major for which they are currently assigned. (You may use the SQL 'join' subclause, or simply express the join as part of the 'where' clause by indicating that you only want records where the primary key of the child table, rider_major, equals the corresponding foreign key of the...
can you please describe how SHM is different from general oscillation?
can you please describe how SHM is different from general oscillation?
1. What is one of the most powerful features of SQL? Operators Functions Table Joins 2....
1. What is one of the most powerful features of SQL? Operators Functions Table Joins 2. A JOIN is a mechanism used to associate tables within a SELECT statement? True False 3. A JOIN can modify existing database tables True False None of the above 4. The WHERE clause acts as a filter to only include rows that match the filter condition. True False 5. The more tables you JOIN the less resources the system uses. True False 6. The...
Think of different kinds of instrumental music or music with words you cannot understand. Can it...
Think of different kinds of instrumental music or music with words you cannot understand. Can it affect your mood? Can it calm you or stress you?
Describe two kinds of scale economies. What different implications do the two kinds of scale economies...
Describe two kinds of scale economies. What different implications do the two kinds of scale economies have for regional economic development?
Describe two kinds of scale economies. What different implications do the two kinds of scale economies...
Describe two kinds of scale economies. What different implications do the two kinds of scale economies have for regional economic development?
ADVERTISEMENT
Subjects
ADVERTISEMENT
Latest Questions
ADVERTISEMENT