Combine Tables Archives

There are many ways you can combine tables together.

This page is a collection of articles going over set operators, joins, sub queries, and common table expressions.

Use set operators when you wish to combine rows from separate tables together.

Joins come in handy when you want to combine columns from two or more tables.

I love using subqueries to avoid hard coding values in my where clause.

Finally, when you combine tables together, your statements can get real funky and unmanageable. Use Common Table Expressions to make the more readable!

All the examples for this lesson are based on Microsoft SQL Server Management Studio and the sample databases AdventureWorks and WideWorldImporters. You can get started using these free tools using my guide Getting Started Using SQL Server.

Right vs Left Outer Join Differences

I recently put together a lesson on the difference between right vs left outer join operators. It is part of my Join Together Now course. Click here get the course at a discount. Outer joins are a great way to match tables, while allowing still including rows from one table that don’t necessarily match to […]

What is a Non-Equi Join in SQL and What is its Purpose?

I recently put together a lesson on non-equi joins. It is part of my Join Together Now course. Click here get the course at a discount. A non-equi join can be used to solve some interesting query problems. You can use a non-equi join to check for duplicate value or when you need to compare […]

How to Make a Join Easier to Read using Table Aliases

I recently put together a lesson on table aliases and multi-table joins. It is part of my Join Together Now course. Click here get the course at a discount. You’ll find that as you write more complicated joins, that the SQL can become harder to read, as you’ll need to qualify column with table names […]

How to use the IN Operator with a SubQuery

In this video we’ll walk you though how to use the IN operator with a Subquery; we’ll show you why you would want to use IN with a subquery. In it’s simplest form the IN statement matches a column values to a list. TRUE is returned if there is a match. A main advantage of […]

SQL Server and Set Operations

In this puzzle, we’re going to about SQL set operations. Set operations allow us to compare rows from two or more tables to arrive at a result. For several classes of problems, is is much easier to use a set operation, than join data. Solving puzzles is a great way to learn SQL. Nothing beats […]

Joins versus Subqueries SQL Puzzle

In this puzzle, we’re going to learn about joins versus subqueries. In many queries you can substitute joins and subqueries. Yet, since each has their strengths, it isn’t wise to do so. Once you have worked through this puzzle you see some of the benefits and disadvantages of joins versus subqueries. Solving puzzles is a […]

Learn How to Combine Data with a CROSS JOIN

A cross join is used when you wish to create combination of every row from two tables. All row combinations are included in the result; this is commonly called cross product join. A common use for a cross join is to create obtain all combinations of items, such as colors and sizes. Note: The series […]

Recursive CTEs Explained

Recursive CTE’s In this article we explore recursive CTE’s (Common Table Expressions). Recursive CTEs are special in the sense they are allowed to reference themselves! Because of this special ability, you can use recursive CTEs to solve problems other queries cannot. Recursive CTEs are really good at working with hierarchical data such as org charts […]

Non Recursive CTEs Explained and Why to Use Them

Introduction to Non Recursive CTEs In this article we explore non recursive CTEs (Common Table Expressions). This is a broad class, and basically covers every form of CTEs except those that call themselves. This other class is called the recursive CTEs; they are covered in the next article. If you’re unfamiliar with CTEs I would […]

Introduction to Common Table Expressions (CTE’s)

Common Table Expressions or CTE’s for short are used within SQL Server to simplify complex joins and subqueries, and to provide a means to query hierarchical data such as an organizational chart. In this set of articles, we’ll introduce you to common table expressions, the two types, and their uses. In this article we’ll introduce […]

What is the Difference between Inner and Outer Joins?

Both inner and outer joins can be used to combine data from two or more tables; however, there are some key differences! Read this article to find out each command’s strengths and when to use them. To get the most of this and our other lessons be sure to practice using the examples! All the […]

What Is the Difference Between a Join and a UNION?

Both Joins and UNIONS can be used to combine data from two or more tables. Read this article to find out each command’s strengths and when to use them. To get the most of this and our other lessons be sure to practice using the examples! All the examples for this lesson are based on […]

Subqueries versus Joins

Joins and subqueries are both be used to query data from different tables and may even share the same query plan, but there are many differences between them. Knowing the differences and when to use either a join or subquery to search data from one or more tables is key to mastering SQL. All the examples […]

Is it possible to use Joins, Subqueries, and Unions in one Query?

A reader recently asked me to help them solve a problem combining data from multiple tables into a single result. What was interesting was that the solution used the three ways to combine data that we have recently covered, namely: JOINS, set operators, and subqueries. Given the comprehensive nature of this problem I thought it […]

Get Ready to Learn SQL Server 23: Using Subqueries in the HAVING Clause

This is the fifth in a series of articles about subqueries. In this article we discuss subqueries in the HAVING clause. Other articles discuss their uses in other clauses. 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 […]

Using A Subquery in the FROM clause

This is the fourth in a series of articles about subqueries. In this article we discuss using a subquery in the FROM clause. Other articles discuss their uses in other clauses. 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 Subqueries in the WHERE Clause

This is the third in a series of articles about subqueries. In this article we discuss subqueries in the WHERE clause. Other articles discuss their uses in other clauses. 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 Subqueries in the Select Statement

This is the second in a series of articles about subqueries. In this article we discuss subqueries in the SELECT statement’s column list. Other articles discuss their uses in other clauses. All the examples for this lesson are based on Microsoft SQL Server Management Studio and the AdventureWorks2012 database. You can get started using these […]

Learn SQL – Introduction to Subqueries

The purpose of this article is to introduce you to subqueries and some of their high-level concepts. There are a lot of details to cover in order to learn sub queries, but you’ll see we cover those in depth in later articles. All the examples for this lesson are based on Microsoft SQL Server Management […]

How do I combine results from more than one table?

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 […]

How to use the EXCEPT operator

How to use the Except Operator The EXCEPT operator is used to exclude like rows that are found in one query but not another. It returns rows that are unique to one result. To use the EXCEPT operator, both queries must return the same number of columns and those columns must be of compatible […]

What is the SQL INTERSECT Operator?

How to use the Intersect Operator The INTERSECT operator is used to combine like rows from two queries. It returns rows that are in common between both results. To use the INTERSECT operator, both queries must return the same number of columns and those columns must be of compatible data types. Note: All the […]

Combine Table Rows Using UNION

Combine Table Rows Using UNION In this lesson we are going to talk about the UNION clause. You can use the UNION clause to combine table rows from two different queries into one result. Unlike a join, which combines columns from different tables, a union combines rows from different tables. Here is an illustration of […]

Learn to use Union, Intersect, and Except Clauses

Learn to use Union, Intersect, and Except Clauses The UNION, INTERSECT, and EXCEPT clauses are used to combine or exclude like rows from two or more tables. They are useful when you need to combine the results from separate queries into one single result. They differ from a join in that entire rows are matched […]

Introduction to Outer Joins

Outer Joins Outer joins are used to match rows from two tables. Even if there is no match rows are included. Rows from one of the tables are always included, for the other, when there are no matches, NULL values are included. The series starts with the article Introduction to Database Joins. All the examples for […]

Inner Join Introduction

An inner join is used when you need to match rows from two tables. Rows that match remain in the result, those that don’t are rejected. The match condition is commonly called the join condition. When the match conditions involve equality, that is matching exactly the contents of one column to another, the join is […]

Introduction to Database Joins

One of the biggest issues beginning SQL writers have is being able to write queries that use more than one table, that is to use database joins. In this series of articles we are going to show you how to write a query that combines, or joins, data from more than one table. Once you […]