TSQL Training: JOINs

Today’s training will be on JOINs.  Here is the outline for the training.

  • —Basic SELECT
  • —INNER JOIN
  • —LEFT OUTER JOIN
  • —RIGHT OUTER JOIN
  • —LEFT OUTER JOIN with exclusion
  • —RIGHT OUTER JOIN with exclusion
  • —SEMI JOIN
  • —ANTI SEMI JOIN
  • —CROSS JOIN
  • —FULL OUTER JOIN
  • —FULL OUTER JOIN with exclusion
  • —CROSS APPLY
  • —OUTER APPLY
  • —Multiple combinations of JOINs.

I will post the video here after the training is complete.

If you are participating in the training, be sure to download my Join Types Poster to follow along with the presentation.  http://stevestedman.com/2015/03/tsql-join-types-poster-version-3/

Here is the recording of the training session:

 

See Also:

 

 

Using The TSQL EOMONTH Function

Here is a quick video training on how to use the T-SQL EOMONTH function on SQL Server. This was originally part of my free SQL query training for the 70-461 certification exam.

The EOMONTH function returns the last day of a given month, or a future or past month by using the second parameter of offset. EOMONTH was introduced in SQL Server 2012, and is also available in SQL Server 2014 or newer.

Here is the sample code to go with the EOMONTH video tutorial:

-- EOMONTH (2012)
DECLARE @date DATETIME
SET @date = DATEFROMPARTS(2012, 11, 10)
SELECT EOMONTH ( @date ) AS Result;

-- next month end of month
SELECT EOMONTH ( @date, 1) AS Result;

-- last month end of month
SELECT EOMONTH ( @date, -1) AS Result;

See Also

TSQL JOIN Types Poster (Version 3)

So many times I have been asked for help with a query, where the questoin really comes down to the understanding of the difference between INNER and LEFT or RIGHT JOINs. I created this poster a few years ago and I keep it posted on the wall at the office. This way when I am trying to explain JOIN types, I just refer to the poster.

I have created the poster below to help describe JOIN types in SQL Server. This had lead to lots of confusion over time, and this is the best way that I have seen to describe them.

If you are looking for the MySQL version of the poster, click here.

Click to Download TsqlJoinTypes.pdf
TsqlJoinTypes
Click to Download TsqlJoinTypes.pdf
The diagram shows the following: INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, SEMI JOIN, ANTI SEMI JOIN, LEFT OUTER JOIN with exclusion, RIGHT OUTER JOIN with exclusion, FULL OUTER JOIN, CROSS JOIN, FULL OUTER JOIN with exclusion, CROSS APPLY, OUTER APPLY, two INNER JOINs, two FULL OUTER JOINs, INNER JOIN and a LEFT OUTER JOIN and two LEFT OUTER JOINs.

If you are interested in printing out the poster, download the TsqlJoinTypes.pdf file and print it.

 

See Also:

Using The TSQL DATEADD Function

Here is a quick video training on how to use the T-SQL DATEADD function on SQL Server. This was originally part of my free SQL query training for the 70-461 certification exam.

And here is the sample code to go with the DATEADD video tutorial:

SELECT DATEADD(month, 1, GETDATE())
SELECT DATEADD(month, -1, GETDATE())
SELECT DATEADD(month, 3, GETDATE());

See Also

Using The TSQL DATEDIFF Function

Here is a quick video training on how to use the T-SQL DATEDIFF function on SQL Server. This was originally part of my free SQL query training for the 70-461 certification exam.

And here is the sample code to go with the DATEDIFF video tutorial:

SELECT DATEDIFF(week, '1/1/2015', GETDATE());
-- does the order matter?
SELECT DATEDIFF(week, GETDATE(), '1/1/2015');


SELECT DATEDIFF(year, '1/1/2015', GETDATE());
SELECT DATEDIFF(quarter, '1/1/2015', GETDATE());
SELECT DATEDIFF(month, '1/1/2015', GETDATE());
SELECT DATEDIFF(dayofyear, '1/1/2015', GETDATE());
SELECT DATEDIFF(day, '1/1/2015', GETDATE());
SELECT DATEDIFF(week, '1/1/2015', GETDATE());
SELECT DATEDIFF(hour, '1/1/2015', GETDATE());
SELECT DATEDIFF(minute, '1/1/2015', GETDATE());
SELECT DATEDIFF(second, '1/1/2015', GETDATE());
SELECT DATEDIFF(millisecond, '1/1/2015', GETDATE());
SELECT DATEDIFF(microsecond, '1/1/2015', GETDATE());

-- to help eliminate the overflow.
SELECT DATEDIFF(millisecond, '3/19/2015', GETDATE());
SELECT DATEDIFF(microsecond, '3/19/2015', GETDATE());

SELECT DATEDIFF(microsecond, '3/19/2015 11:00am', GETDATE());

See Also

FULL OUTER JOIN vs CROSS JOIN

As I have been working on the SQL Server JOIN Types poster, I have received several questions around the difference between a CROSS JOIN, and a FULL OUTER JOIN. After looking at the Venn diagrams for the two, they are both shown as the same, however they are not the same by any means.

Yes, they both include all rows from both the LEFT and RIGHT side of the JOIN, however they are matched up or JOINed in a very different way.

Lets take a look, first at the Venn diagrams, you can see below that the Venn diagrams show that all rows from Table1 are included in the results, and all rows from Table2 are included in the results. This is indeed correct, the differences come in the number of rows and how the results are matched up.

CrossJoinVennFullOuterJoinVenn

 

Now lets take a look at some sample cod that shows the differences. First we will create a database and create a couple tables to use for the demo, and each table has 3 rows inserted to it:

CREATE DATABASE [QueryTraining];
GO
USE [QueryTraining];
GO

CREATE TABLE People
(
	id   INTEGER IDENTITY NOT NULL,
	Name VARCHAR(100) NOT NULL,
    fk   INTEGER NULL
PRIMARY KEY (id)
) ;

CREATE TABLE Colors
(
	id   INTEGER NOT NULL,
	FavoriteColor VARCHAR(100) NOT NULL
) ;

INSERT INTO Colors (id, FavoriteColor) VALUES
    (1, 'red'),
    (2, 'green'),
    (3, 'blue');

INSERT INTO People (Name, fk) VALUES
    ('Steve', 1),
    ('Aaron', 3),
    ('Mary', NULL);

Look at this as a database to store people and their favorite color, in this example, there are 3 people , and 3 colors. One person does not have a favorite color, and one color has not been favorited by anyone.

Now lets take a look at the differences between the two.

CROSS JOIN

CrossJoinVenn

The CROSS JOIN gives you the Cartesian product of the two tables, by matching every row from one table with every row from another table. If there are X rows in the first table, and Y rows in the second table, the result set will have exactly X times Y rows. Notice on the CROSS JOIN, there is no ON clause specified. This is because there is not matching.

CROSS JOIN Sample

SELECT *
  FROM People p
  CROSS JOIN Colors c;

Which gives us the following result set:

CrossJoinResults

 

In the above result set, you can see that for each row in the People table (3) it was matched up with each row  from the Colors table (3) which gives us a total of 3 x 3 = 9 rows in the result set.

FULL OUTER JOIN

Now for the full outer JOIN, this is like asking for a LEFT OUTER JOIN and a RIGHT OUTER JOIN in the same query. You are asking to get every row from Table1 matching what could be matched in Table2, for those that don’t match on the Table1 side, just display the Table2 side, with NULLs on the LEFT side, and for those that don’t match on the TABLE2 side, just display the Table1 side with NULLs on the RIGHT side.

FullOuterJoinVenn
FULL OUTER JOIN Sample

SELECT *
  FROM People p
  FULL OUTER JOIN Colors c
    ON p.fk = c.id;

In this case everything from Table1 will be represented, and everything from Table2 will be shown, but they will be matched up base on the ON clause, rather than with the CROSS JOIN which gives every possible combination.FullOuterJoinResults

I hope that this helps answer some of the questions with the differences between a CROSS JOIN and a FULL OUTER JOIN.

If you haven’t already taken a look, please check out the free download of the SQL Server JOIN Types poster.

For More Information: