How do I combine results from several SQL tables (hint: there are three ways)

Kris Wenzel

·

·

, , ,

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 today learning SQL with my Guide Getting Started Using SQL Server.

Table of contents

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 Results

Database tables are organized into rows and columns within a relational database.  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 – I sometimes call these nested queries.

Joins

I like to think of joins as the glue that puts 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.  By matching columns from each table the data is combinesd.

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.

InnerSQL Inner Join Joins only return a resulting row if the join condition matches in both tables.  To match the primary key of one table a foreign key in another use an inner join.

The second type of join is an outer join.  SQL left 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 exist in either table combined.  The joins return combinations of matches.  If you join two tables, one containing 5 row, and the other 10, the result may contain anywhere from 0 to 50 rows depending on the join condition.

Read More: SQL Joins – The Ultimate Guide >>

Unions

Combine results, row-by-row, with a UNION.  This is one of three set operators.

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 combined list for a catalog.  You can either elect to have the end result be a unique listing for the combined 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

The other set operators are:

  • 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 in one table, but not 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.

Read More: Set Operators – The Ultimate Guide

Subqueries

Sometime we call subqueries “nested queries.”  This is because the subquery is inside, or “nested within” the main query.  Subqueries can be confusing.  Subqueries are confusing and hard to learn since they versatile and used in many locations.

For example, here are some areas you may see a subquery to combine results:

  • SELECT clause – Used to return a value.  For instance, if you’re querying a sales table, you could include the total sales by return a sum of all sales from within a subquery.
  • WHERE clause – Use single result subqueries in comparisons, or table results with IN or EXISTS.
  • HAVING clause – I mostly see them used to with comparison operators. Many times you’ll use a subquery here to calculate a sum or average and compare that to an summation within the group.

Read More: The Ultimate Guide to Subqueries >>

Example Subquery

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 subquery 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.

25 responses to “How do I combine results from several SQL tables (hint: there are three ways)”
  1. Joe

    *couldn’t even.

    Reply
  2. acna

    alert(“hello world”);

    Reply
    1. acna

      alert(“awww.. so naive..”);

      Reply
  3. acna

    kjc

    Reply
  4. Rajkumar

    hi please help as i have how to find % increase in average.

    I have avg from table 1 and avg from table 2 by using union in select command but i need to see % increase of avg also in same query please tell how to do it.

    Reply
  5. Nisa Humaira

    Is it okay if I want to combine data from three unique ID from a table (Ex: P01, P02, P03) and join with another table with a unique ID. Means the P01, P02, P03 become under one ID in another table.

    Reply
  6. Shailendra Mishra

    DECLARE @cols AS NVARCHAR(MAX),@query AS NVARCHAR(MAX)

    select @cols = STUFF((SELECT ‘,’ + QUOTENAME(designation)
    from MyTable
    group by designation
    order by designation
    FOR XML PATH(”), TYPE
    ).value(‘.’, ‘NVARCHAR(MAX)’),1,1,”)

    set @query = N’SELECT Row, ‘ + @cols + N’ from
    (
    select ”SS” Row, SS AS Value , designation from MyTable
    UNION ALL
    select ”AS” Row, [AS] AS Value , designation from MyTable
    UNION ALL
    select ”Vac” Row, Vac AS Value , designation from MyTable
    ) x
    pivot
    (
    max(Value) for designation in (‘ + @cols + N’)
    ) p ‘
    exec sp_executesql @query;

    For more details: https://stackoverflow.com/questions/60407997/convert-row-into-column-when-number-of-row-is-not-fixed/60408599?noredirect=1#comment106864045_60408599

    Reply
  7. S

    thank you for your help, very helpful

    Reply
  8. sharnila

    Hi,

    i have three tables which have Serial no in common. i have Firstname in first table, secondname in second table and thirdname in third table.how do i join three tables so that the result is a single column of name from all three tables

    Output should be:

    S.NO|Name

    Reply
    1. Travis Pluff

      Seems like a weird case but I guess just join using UNION

      If you leave out the ALL after Union it will only select Distinct values by default…

      SELECT
      No
      ,Firstname AS Name
      FROM FirstNameTable

      UNION ALL

      SELECT
      No
      ,Secondname As Name
      FROM SecondNameTable

      UNION ALL

      No
      ,Thirdname As Name
      From ThirdNameTable

      Reply
      1. Kris Wenzel

        Hi,

        UNION is great when you want to combine ROW from two or more tables. Just keep in mind that UNION does not JOIN tables… :)

        Reply
    2. Shivam Barve

      Not sure whether you’re still looking for a solution, however I found the solution here:
      https://stackoverflow.com/questions/39523580/select-from-multiple-tables-one-to-many-relation

      Reply
  9. sai

    how o join the 4 tables in 1 table and give the final table name. could u please give the answer for my question.

    Reply
    1. Kris Wenzel

      When using a query the results are returned as a rowset and aren’t stored permanently as a table. If you want to have a query with a join and place those results into a table you can use the INSERT statement. Check out this article: https://www.essentialsql.com/sql-insert-statement/ near the bottom there is a section on inserting from other tables.

      Reply
    2. Travis Pluff

      Use a View… write the query to produce the results as you want them then create it as a View. Then use the View.

      For reporting purposes you don’t need or want extra tables, you want a View.

      Reply
  10. sai

    i am having 4 tables and i want join the 4tables in 1 single table

    Reply
  11. AARTI

    I have ages calculated in my table. all I need is 2 different sets of age groups from the age field. please can someone suggest how can I do this?

    Reply
    1. Travis Pluff

      CASE WHEN [Age] >=1 AND [Age] =11 AND [Age] =21 AND [Age] <=30 THEN '21-30 Years'
      ELSE 'REALLY OLD'
      END AS 'Age Group'

      Or something like that.

      Reply
      1. Kris Wenzel

        Hi,

        You can try something similiar to:

        SELECT name, CASE age
        WHEN age <= 12 THEN 'youth' WHEN age >12 AND age < 18 THEN 'teen ager' WHEN age >= 18 and < 25 THEN 'young adult' WHEN age >= 25 ‘adult’
        Else ‘unknown’
        End as [Age Group]
        From people

        Here is an article you can also look at on SQL Case Statements.

        Reply
  12. Anoosha

    i want to combine two result sets where two result sets are from same table just with different conditions but need to combine them in a way that
    customer,Account1,Account2
    first query gives me customer,account1(account column alias as account1) – based on some condition
    second query gives me customer,account2(account column alias as account2) – based on different condition
    now on joining them i need output like
    customer Account1 Account2
    1 A1
    2 A2

    something like that, could someone help me

    Reply
    1. Kris Wenzel

      I think for this example you can use UNION ALL. Here is an overall structure of the query you can use:
      SELECT customer, Account as Account1, NULL as Account2
      FROM accounts
      WHERE AccountType = 1
      UNION ALL
      SELECT customer, NULL, Account
      FROM accounts
      WHERE AccountType = 2

      Reply
  13. Roman

    Hello, i have a question. I have table users that have userid, fname, lname, email, phone and table units that have unit number, userid, currentOwner.

    i would like to publish the result in this view:

    user name | all unit numbers for the userid | phone | email

    any help please is appreciated

    Reply
    1. Kris Wenzel

      Hi,

      Beyond using joings to combine the columns from the two tables, you’ll need a way to concatenate multiple rows into a list for the unit numbers.

      This article should help to show you how to do that using the FOR XML clause.

      https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv

      Reply
  14. nar

    I have 2 tables. first table contains name and age and the second table contains class and roll num. I have a 3rd table which contains all these 4 columns. Now I want to fill the 3rd table with the 1st and 2nd table’s data.

    Please give the suggestion

    Reply
    1. Kris Wenzel

      From your question you want to insert some rows into a table based on data from more than one table. To do this you can use the INSERT statement in combination with SELECT INNER JOIN query.
      Here are the tables I’m using in the example.

      Person
      Name
      Age
      Address

      Class
      Class
      RollNum
      Location

      Enrollment
      Name
      Age
      Class
      RollNum

      FinalTable
      Name
      Age
      Address
      Class
      RollNum
      Location

      The first step is to create a SELECT clause to test the join. Let make sure we get all the data combined the way we want before we move on to combining it with the INSERT. Here is the select statement I created:

      SELECT P.Name, P.Age, P.Address, C.Class, C.RollNum, C.Location
      FROM Person P
      INNER JOIN Enrollment E ON P.Name = E.Name and P.Age = E.Age
      INNER JOIN Class C ON C.Class = E.Class and C.RollNum = E.RollNum

      Now after you have tested it and made sure it is returning all the rows you wish, we can change it into the INSERT statement. We’ll take the results of this SELECT and insert them in the table FinalTable.

      INSERT INTO FinalTable (Name, Age, Address, Class, RollNum, Location)
      SELECT P.Name, P.Age, P.Address, C.Class, C.RollNum, C.Location
      FROM Person P
      INNER JOIN Enrollment E ON P.Name = E.Name and P.Age = E.Age
      INNER JOIN Class C ON C.Class = E.Class and C.RollNum = E.RollNum

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

More from the blog

MySQL PostgreSQL SQLite SqlServer