Database Joins
You’ll find article covering several types of database joins including Inner Joins and Outer Joins.
It is important to learn about both inner and outer joins and they have different matching behavior.
I would start out with our article Introduction to Database Joins.
This first article introduces the concept of joining tables. The focus is going to be more on the type of joins, not necessarily their syntax. The later articles focus on the various types of joins. Through the narrative and examples you’ll become very comfortable with each one.
Some of our more popular articles include:
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
Latest Posts
-
How to do an Impossible Join with String Split
In this article I’ll show you how to use STRING_SPLIT() to join two tables. I call this the impossible join. I’ve come across this situation several times and always when dealing with data imported from two different sources. In most case it has to do with users inputting data inconsistently. Let me show you. Watch…
-
Inner Join vs Outer Join Clauses
Its is easy to get confused between inner join vs outer join clauses. Both inner and outer joins are 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…
-
Mastering SQL FULL JOIN: A Comprehensive Guide
The SQL FULL JOIN combines results from a left and right outer join into one result. Or in other words, it is an inner join including unmatched rows from both the left and right tables. This is the key difference between a SQL FULL JOIN and inner join. Where an inner join returns rows matching the join condition, a FULL outer join guarantees all table rows…
-
SQL Right Join
The SQL RIGHT JOIN Includes all rows from the Right table and those that match from the left. For that doesn’t match from the right, NULL is returned in the corresponding columns. This is the key difference between a SQL RIGHT JOIN and INNER JOIN. Where an inner join only returns rows matching the join condition, in a Right outer join, it’s guaranteed all the Right tables rows are included in the…
-
SQL Left Join
The SQL LEFT JOIN Includes all rows from the left table and those that match from the right table. When the right table doesn’t match the join condition, the query returns null values for those columns. This is the key difference between a LEFT JOIN and inner join. Where an inner join only returns rows matching the join condition, a SQL…
-
SQL Joins – The Ultimate Guide
SQL Joins are important to master. As you progress from a beginner to advanced beginner, you’ll soon need to combine data from more than one table. To do this, you’ll one of several SQL join types. In this series of articles I’ll show you how to write a query that combines, or joins, data from…
-
SQL Cross Join
Use SQL cross joins when you wish to create a 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. What is a…
-
SQL Inner Join
Use an SQL INNER JOIN 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 sql inner…
-
SQL Self Join
In a SQL self join the table is joined to itself. You can think of it being like an inner join. The idea is to match rows using a join condition. Here is the general syntax for a SQL self join: SELECT a.column1, b.column2, …FROM table1 aINNER JOIN table1 b ON a.matchColumn = b.matchColumn This…
-
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…
-
What Is the Difference Between a Join and a UNION?
Use joins and UNIONS 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 are sure to practice using the examples! All the examples for this lesson are based on Microsoft SQL Server…
-
Subqueries versus Joins
Use both Joins and subqueries to query data from different tables. Though they may even share the same query plan, 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 for this…
-
SQL Server Hierarchical Query using the hierarchyid type
In this SQL minute we’ll look at how to create a SQL server hierarchical query using the hierarchyid data type. This is a two-part article. In part one you were introduced to the hierarchyid type. In this article we dig deeper into the hierarchyid functions. If you’re studying for the 70-761 exam, you’ll find there aren’t…
-
Use hierarchyid to query Hierarchical data
In this SQL minute we’ll look at how you can use hierarchyid to query hierarchy data. This is a two-part article. In this article we look at how to do one query to get portion of the hierarchy. Next week, we will look to see how to query the entire tree. For this problem we…
-
SQL Pivot Table – Learn to Create in Six Steps
In this episode of SQL Minute I want to share with you how to create a pivot or cross tab chart. I’ll show you the steps using an example from the adventure works database. I get so many questions on how to create these! Once you get the steps down, you’ll see they are not…
-
Left Join versus Right Join Comparison
I recently put together a lesson on the difference between left join versus right join operators. Outer joins are a great way to match tables, while allowing still including rows from one table that don’t necessarily match to another table. The good news is that when it comes to right outer joins or, its twin,…
-
What is a Non-Equi Join in SQL and What is its Purpose?
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 one value in a table falls within a range of values within another. You can learn more about this this video, it’s transcript follows: What is…
-
How to Make a Join Easier to Read using Table Aliases
I recently put together a lesson on table aliases and multi-table joins. 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 to avoid ambiguity. To make you SQL more compact and more readable you can create table aliases.…
-
Full Outer Join as Union
In this puzzle, we’re going to learn how to write a SQL UNION without using Set Operations. Set operations allow us to compare rows from two or more tables to arrive at a result. For several classes of problems, it is much easier to use a set operation, than join data. But can we do…
-
Joins vs Subqueries SQL Puzzle
In this puzzle, we’re going to learn about joins vs 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 vs subqueries. Solving puzzles is a…
-
Rewrite Subquery as a Join
In this puzzle, we’re going to learn how to rewrite a subquery using inner joins. Knowing about a subquery versus inner join can help you with interview questions and performance issues. Though subqueries have unique abilities, there are times when it is better to use other SQL constructs such as joins. By reading this article…
-
Use SQL Server to Create a Cross Tab Query
In this puzzle, we’re going to learn how to create a cross tab query using SQL Server. Cross tabs are a great way to summarize data. And given that Business Intelligence is a hot topic, knowing how to create one is key. We’ll learn to create a cross tab query. Before you read the entire…
-
How can I find Duplicate Values in SQL Server?
In this article find out how to find duplicate values in a table or view using SQL. We’ll go step by step through the process. We’ll start with a simple problem, slowly build up the SQL, until we achieve the end result. By the end you’ll understand the pattern used to identify duplicate values and…