Module 3: SQL Joins and SET Operators

Previous Tutorial - Next Tutorial

Welcome to this course SQL server for beginners. In this first module, we will understand the different types of joins available in SQL Server to fetch the data from multiple tables. Many times the data that we needed to query is recited into multiple tables when a relational database is designed, the tables are typically created with defined relationships between them. When the data is queried then this join operation often utilizes these relationships.

Basically, database tables are related to each other with the keys. We use these keys as a relationship in equal joins. By using joins you can retrieve data from two or more tables based on logical relationships between the tables. Join provides a means to reassemble the data back into meaningful information. First, we will start with the inner joins. This is the most common type of join operation and used most commonly. In this part, we will see how with an inner join, we can match up rows in one table with the corresponding rows in the other table where associated columns contain the same values.

We will also explore the general structure or syntax of this inner join and then we will understand the Outer joins. There are three Outer joins available in SQL Server. The first one is left Outer join, the second one is right Outer join and the third one is full outer join. We will explore the general structure and syntax and see how with an Outer join you can request to return all rows from the left table as well as the right-hand side tables. Even if there are no matching rows on either side based on the search condition and then we will understand the Cross joins though this join is not the most commonly used one but you can say this is the grandfather of all joins.

When using a cross join you don't need columns for the join to match values which means this join does not need any condition to join two tables. This join is also known as Cartesian join which means a Cartesian product of both the tables. The next one is the Self join, we will understand this self join by joining the table with itself. Self join is not a kind of SQL join as mentioned above. It is a special join in which you use the inner join or outer joins to join the tables with themselves.

Then we will explore the Multi-joins query in which we will use all of the joins mentioned above in one query to form one meaningful result. Then we will understand the different types of Set operators available in SQL server. Set operators operate on two result sets of queries comparing complete rows between the results depending on the result of the comparison and the operator used the set operator determines whether to return rows or not.

SQL Server supports the following operator, UNION, UNION ALL, INTERSECT, and EXCEPT. We will explore each one to see the difference between them. By the way, the term set operator here is not a precise term to describe the UNION, INTERSECT, and EXCEPT operators rather relational operators are a better term.

However, the official t-SQL documentation used the term set operators and therefore I am using this terminology over here, and then we will explore the fundamentals of the Subqueries. In SQL server several forms of Subqueries exist ranging from expressions that return a single or a scalar value to a multi-row result set. We will learn these several examples of each type in SQL server.

Many queries can be returned in more than one way, traditional SQL statements return for other database products often use subqueries whereas the SQL server leaned towards the ANSI standard join expression. Most SQL server professionals will tell you that if you have the option to choose between using a subquery and a join then the join will execute faster.

Generally speaking, I think this is true but again it depends on the expression and the other condition. By the end of this module, you will be having a basic understanding of the different types of joins available in SQL servers. You will also know the difference between each set operator like UNION, UNION ALL, INTERSECT, etc.

Web API for developers Free Trial Offline SDK

Here's SQL video tutorial:

Other useful tutorials:

• General SQL Introduction
• Module 1: Getting Started with SQL Server
• Module 1: SQL Server Management
• Module 1: Essential SQL Commands
• Module 1: SQL Introduction Essentials
• Module 2: Learning SQL Overview
• Module 2: Logical Query Processing Order
• Module 2: Select Statement Fundamentals
• Module 2: Filtering Data with WHERE Clause Overview
• Module 2: Filter Data with WHERE Clause - Part 1
• Module 2: How to Filter Data with WHERE Clause - Part 2
• Module 2: Filter PDF Data Using WHERE clause - Part 3
• Module 2: Sorting Data Using ORDER BY Clause
• Module 2: Grouping Data with GROUP BY Clause Part 1
• Module 2: Grouping Data with GROUP BY Clause Part 2
• Module 2: Grouping Data with GROUP BY Clause Part 3
• Module 2: Limiting Data With TOP Clause
• Module 2: Summary and Quiz
• Module 3: SQL Joins and SET Operators
• Module 3: Understanding Inner Join
• Module 3: Understanding Outer Join
• Module 3: Cross Join Explained
• Module 3: Self Join Explained
• Module 3: UNION and UNION ALL
• Module 3: INTERSECT and EXCEPT
• Module 3: Understanding Sub-Queries
• SQL Analytic Functions