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.

Joins versus Subqueries SQL Puzzle

Joins versus Subqueries

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

Continue reading

Learn How to Combine Data with a CROSS JOIN

Cross Join Meme

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

Continue reading

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

Continue reading

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

Continue reading

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

Continue reading
1

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

Continue reading
4

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

Continue reading

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

HAVING Clause Subquery

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

Continue reading
4

Using A Subquery in the FROM clause

Derived Tables in From Statement

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

Continue reading
1

Using Subqueries in the WHERE Clause

Sub query in 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 […]

Continue reading
8

Using Subqueries in the Select Statement

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

Continue reading
4

Learn SQL – Introduction to Subqueries

Introduction to Sub Queries

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

Continue reading
4

How do I combine results from more than one table?

Combine data in SQL

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

Continue reading
12

Learn to use Union, Intersect, and Except Clauses

Intersection, Exception, Union

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

Continue reading