How Do I Combine Results from Several SQL Tables (Hint: There Are Three Ways)

Many times in a relational database, the information you want to show in your query is in more than one table. This begs the question, “How do you combine results from more than one table?”

All the examples for this lesson are based on Microsoft SQL Server Management Studio and the AdventureWorks2012 database. You can get started using these free tools using my Guide Getting Started Using SQL Server.

What Are the Ways I Can Combine Results From More Than One Query?

SQL wouldn’t be a very useful language if it didn’t provide an easy means for you to combine results from more than one query. Fortunately, there are three main ways you can combine data from multiple tables. We’ll go over these briefly here and provide links to more in-depth articles.

Three Main Ways to Combine Data

Data in relational database tables are organized into rows and columns. As we investigate ways to combine data, keep in mind that the end result will be to either add more columns to a result, perhaps from another related table, or rows, by taking a set of rows from two or more tables.

When most people learn to combine data, they learn about:

1. JOIN – You can use joins to combine columns from one or more queries into one result.
2. UNION – Use Unions and other set operators to combine rows from one or more queries into one result.
3. Sub Queries – Sometimes called nested queries, these can be used to perform a separate search in the database showed results can be used in another query.

Joins

I like to think of joins as the glue that put the database back together. Relational databases are usually normalized to make the data easier to maintain and to improve performance, but the end result is information is separated into many tables. You can use Joins to recombine that information back together into a more human readable format. The data is recombined by matching columns from each table.

In all cases, joins require two main ingredients: Two tables and a join condition. The tables are what we will use to pull the rows and columns and the join condition is how we intend on matching the columns between tables.

Example JOIN

SELECT  Person.FirstName,
        Person.LastName,
        PersonPhone.PhoneNumber
 FROM   Person.Person
        INNER JOIN Person.PersonPhone
        ON Person.BusinessEntityID = 
           PersonPhone.BusinessEntityID

There are two main types of joins, Inner Joins and Outer Joins.

Inner Joins only return a resulting row if the join condition matches in both tables. Inner joins are mainly used to match the primary key of one table with a foreign key in another.

The second type of join is an outer join. Outer joins always return at least one row for the main table, referred to as the Left or Right table, and null values in the corresponding columns of the non-matching column. Outer joins are useful for finding non-matching data.

It is important to note that joins can return more rows than those that exist in either table combined. The joins return combinations of matches. If you join two tables, on containing 5 row, and the other 10, the result may contain anywhere from 0 to 50 rows depending on the join condition.

Unions

A UNION is used to combine the rows of two or more queries into one result. Union is called a set operator.

There are some special conditions that must occur in order for a union to work. First, each query must have the same number of columns. Second, the data types of these columns must be compatible. Generally speaking, each query must return the same number and type of columns.

A practical example of union is when two tables contain part numbers and you want to create a combine list for a catalogue. You can either elect to have the end result be a unique listing for the combine query or if you use UNION ALL return all rows from each table.

Example UNION

SELECT C.Name
FROM   Production.ProductCategory AS C
UNION
SELECT S.Name
FROM   Production.ProductSubcategory AS S

In addition to Union, there are a couple of other handy set operators:

• INTERSECT – You can use this to only return rows that are common between two tables.
• EXCEPT – You can use this to return rows that exist on one table, but aren’t found in another.

As you go on to learn more SQL, you find that you can use joins to write equivalent statements for Intersect, and Except, but there are no equivalents for Union.

Sub Queries

Sub queries are sometimes called nested queries. They are queries defined inside of other queries. Sub queries can be confusing. I think a lot of this stems for the fact they can be used in many places in a SQL select statement, and for several purposes!

For example, here are some areas where you may see a sub query:

• SELECT clause – Used to return a value. For instance, if you’re querying a sales table, you could include the total sales by returning a sum of all sales from within a sub query.
• WHERE clause – Sub queries can be used in the where clause in comparisons. You could set up a comparison to compare sales to the overall average. The overall average would be returned from a sub query. You can also use sub queries in membership operators such as IN. Rather than hard-coding the in clause, you can use a sub query to make it more dynamic.
• HAVING clause – A single value from a sub query is included in the HAVING clause comparisons.

Example Sub query

SELECT SalesOrderID,
       LineTotal,
       (SELECT AVG(LineTotal)
        FROM Sales.SalesOrderDetail) AS AverageLineTotal
FROM   Sales.SalesOrderDetail

When used in select clauses and comparison operators such as equals, greater than, and less than, a sub query can only return one row. If used in conjunction with a membership operator, such as IN, it is OK for the query to return one or more rows.

The post How do I combine results from several SQL tables (hint: there are three ways) appeared first on Essential SQL.