From the course: Querying Microsoft SQL Server 2022

Table joins and relationships - SQL Server Tutorial

From the course: Querying Microsoft SQL Server 2022

Start my 1-month free trial Buy for my team

Table joins and relationships

“

- [Instructor] Relational databases are efficient for storing data because they minimize redundancy. They do this by splitting information up and storing it in multiple related tables. This allows the database to compartmentalize data but it does require users to join records back together again when they want to review information that's been spread out across several tables. There are four different types of joins that you can make between two related tables in your SQL queries. They're an inner join, a left outer join, a right outer join, and a full outer join. Let's talk about each one in order. The first, and by far the most common, join that you can create between two related tables is called an inner join. In fact, this is the default join type in SQL Server. So, if you're not specific about what kind of join you're asking for, the query engine will assume that it's an inner join. This is how it works. Assume that we have one table of records, and it has a column of data that relates to a second table. Each of these circles represents all of the records in each table and the overlapping area represents the records that share a relationship across both tables. When you create an inner join in a query, the results will only display rows where the information can be found in both tables. Any records from table A that doesn't relate to a record in table B are excluded, and any record in table B that doesn't relate to a record in table A is also excluded. Within inner join, you only get the data if there's a matching value found in both tables. The second most common join is called a left outer join, or sometimes it's simply called a left join. Like with the inner join, we have two tables with an overlapping set of records that share a common value. When you create a left join, the results will display every record from the table A, regardless of whether they have matching values in table B or not. Then for the records that do have matching values, you'll also get that data from the second table. If there's a record in table A that doesn't have a match in table B, the results will display null values in the columns from Table B. The right outer join, or simply a right join, is the opposite of a left joint. Again, we have the same two tables with an overlapping set of records. A query that uses a right join will return all of the records from table B, regardless of whether they have matching records in table A or not. Then, the results will display the matching values from table A for all of the records that have a match and no values for all of the records that do not. And finally, that brings us to the full outer join. This join takes the same two tables with overlapping values, and it returns every record from both tables, regardless of any matching. Any row in one table that doesn't have a match in the other will show no values for those columns. Essentially, table joins are used to filter records out of the results based on the presence or absence of matching data. The inner join discards unmatched rows. Left joins discards rows from the right table that don't have a match in the left. Right joins, discard rows from the left table that don't have a match in the right. And full joins return everything, regardless of whether there's a match or not. So let's put all of this into practice and explore some of the related data in the AdventureWorks database by joining tables together and looking at the results.

Contents