Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

SQL Basics in MS SQL Server

Introduction
Very simple subqueries
Subqueries with multiple results
Correlated subqueries
Other subqueries
20. Subqueries in the FROM clause

Instruction

Good job! Now, queries can also be used in other places. We can, for example, use a subquery instead of a table in the FROM clause. Take a look:

SELECT
  *
FROM City
INNER JOIN (
  SELECT
    *
  FROM Country
  WHERE Country.Area < 1000
) AS SmallCountry
  ON SmallCountry.ID = City.CountryID;

The above query returns information about the cities of small countries. Of course, there is no SmallCountry table in our database, so... we create it 'on the fly' with a subquery in the JOIN clause. Of course, we need a name for the table the subquery returns so that we may reference it in the main query, so we use an alias with the AS keyword. In the end, the query shows cities together with their countries, provided that each country has an area of less than 1000.

Remember how joining two tables works? We need the condition in the ON clause. Of course, we could put two tables in the FROM clause, but the current layout is more elegant.

Exercise

Show mountains together with their countries. The countries must have at least 50000 people.

Stuck? Here's a hint!

Type:

SELECT
  *
FROM Mountain
INNER JOIN (
  SELECT
    *
  FROM Country
  WHERE Country.Population >= 50000
) AS CrowdyCountry
  ON CrowdyCountry.ID = Mountain.CountryID;