Chaining FULL JOINs

As you have seen in the previous chapter on INNER JOIN, it is possible to chain joins in SQL, such as when looking to connect data from more than two tables.

Suppose you are doing some research on Melanesia and Micronesia, and are interested in pulling information about languages and currencies into the data we see for these regions in the countries table. Since languages and currencies exist in separate tables, this will require two consecutive full joins involving the countries, languages and currencies tables.

This is a part of the course

“Joining Data in SQL”

Exercise instructions

Complete the FULL JOIN with countries as c1 on the left and languages as l on the right, using code to perform this join.
Next, chain this join with another FULL JOIN, placing currencies on the right, joining on code again.

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

SELECT 
	c1.name AS country, 
    region, 
    l.name AS language,
	basic_unit, 
    frac_unit
FROM countries as c1 
-- Full join with languages (alias as l)
___ 
-- Full join with currencies (alias as c2)
___ 
WHERE region LIKE 'M%esia';

Edit and Run Code

This exercise is part of the course

Joining Data in SQL

BeginnerSkill Level

4.4+

Level up your SQL knowledge and learn to join tables together, apply relational set theory, and work with subqueries.

Start Course for Free

In this chapter, you’ll be introduced to the concept of joining tables and will explore all the ways you can enrich your queries using joins—beginning with inner joins.

Exercise 1: The ins and outs of INNER JOIN Exercise 2: Your first join Exercise 3: Joining with aliased tables Exercise 4: USING in action Exercise 5: Defining relationships Exercise 6: Relationships in our database Exercise 7: Inspecting a relationship Exercise 8: Multiple joins Exercise 9: Joining multiple tables Exercise 10: Checking multi-table joins

After familiarizing yourself with inner joins, you will come to grips with different kinds of outer joins. Next, you will learn about cross joins. Finally, you will learn about situations in which you might join a table with itself.

Exercise 1: LEFT and RIGHT JOINs Exercise 2: Remembering what is LEFT Exercise 3: This is a LEFT JOIN, right?Exercise 4: Building on your LEFT JOIN Exercise 5: Is this RIGHT?Exercise 6: FULL JOINs Exercise 7: Comparing joins Exercise 8: Chaining FULL JOINs

Exercise 9: Crossing into CROSS JOIN Exercise 10: Histories and languages Exercise 11: Choosing your join Exercise 12: Self joins Exercise 13: Comparing a country to itself Exercise 14: All joins on deck

In this chapter, you will learn about using set theory operations in SQL, with an introduction to UNION, UNION ALL, INTERSECT, and EXCEPT clauses. You’ll explore the predominant ways in which set theory operations differ from join operations.

Exercise 1: Set theory for SQL Joins Exercise 2: UNION vs. UNION ALL Exercise 3: Comparing global economies Exercise 4: Comparing two set operations Exercise 5: At the INTERSECT Exercise 6: INTERSECT Exercise 7: Review UNION and INTERSECT Exercise 8: EXCEPT Exercise 9: You've got it, EXCEPT...Exercise 10: Calling all set operators

In this closing chapter, you’ll begin by investigating semi-joins and anti-joins. Next, you'll learn how to use nested queries. Last but not least, you’ll wrap up the course with some challenges!

Exercise 1: Subquerying with semi joins and anti joins Exercise 2: Multiple WHERE clauses Exercise 3: Semi join Exercise 4: Diagnosing problems using anti join Exercise 5: Subqueries inside WHERE and SELECT Exercise 6: Subquery inside WHERE Exercise 7: WHERE do people live?Exercise 8: Subquery inside SELECT Exercise 9: Subqueries inside FROM Exercise 10: Subquery inside FROM Exercise 11: Subquery challenge Exercise 12: Final challenge Exercise 13: The finish line