SQL Server - Cross join with where / on condition

Question

I have a table with monthly data for employees that I'd like to do cross joins but only after making sure they're in the same month. Table looks like this:

  Month      Emp_id
1/1/2017      123
1/1/2017      234
1/1/2017      345
1/1/2017      456
2/1/2017      123
2/1/2017      234
2/1/2017      345
  ...

I'd like to do something like this:

select *
from t1
cross join t1 as t2
   on t1.Month = t2.Month

Is there a way to do this?

EDIT:

To elaborate, if I have 100 employees in each month and 12 months I'm looking to get an output table with 120,000 rows (100 * 100 Cartesian for each month, times 12 (1 for each month) instead of doing a full Cartesian which would be 1,440,000.

Solution

Yes, it is called an inner join:

select *
from t1 inner join
     t1 t2
     on t1.Month = t2.Month

You could express the same thing using where and cross join, but I think an inner join is better than:

select *
from t1 cross join
     t1 t2
where t1.Month = t2.Month;

Note that you are using select *, which means that you will have duplicate column names and not know which t1 they are coming from. If that is an issue, ask another question.

Comments

[Larnu]

You mean an INNER JOIN? A CROSS JOIN is used to create a cartesian product, you don't use an ON clause with it. Edit: you could use a WHERE clause, however then the CROSS JOIN implicitly becomes an INNER JOIN, and then using the correct syntax would be better.

[screechOwl]

@Larnu - yes - is there a way to create a cartesian but only within a given month?

[Larnu]

I don't actually understand what you mean. Sorry. You might want to try to elaborate more in your question.

[screechOwl]

Will inner join give me cartesian within each month?

[MatBailie]

@screechOwl - An INNER JOIN takes every row in the left hand table (t1) and matches it against every row in the right hand table (t2) where the join predicate (t1.Month = t2.Month) evaluates to TRUE. I strongly recommend simply going through a basic SQL Tutorial which will elaborate on the for you.