CROSS JOIN in T-SQL in SQL Server 2012

The type of SQL joins are: cross join, inner join and outer join.
What is this cross join?

Begin with prepare two tables with data. It is example:

use mybase;

create table tab1 
(
	id int primary key,
	name varchar(100) not null
);

create table tab2 
(
	id int primary key,
	name varchar(100) not null,
	if_is bit
);

create table tab3 
(
	id int primary key,
	name varchar(100) not null
);

insert into tab1 values(1,'one1');
insert into tab1 values(2,'two1');
insert into tab1 values(3,'three1');

insert into tab2 values(1,'one2',0);
insert into tab2 values(2,'two2',1);
insert into tab2 values(3,'three2',1);

insert into tab3 values(1,'one3');
insert into tab3 values(2,'two3');
insert into tab3 values(3,'three3');

When You get all data from these tables You see:

SELECT * FROM tab1;
SELECT * FROM tab2;
SELECT * FROM tab3;

The cross join adjusts every record from first table with every record from second table.
It is example get two columns from first table and two columns from second table:

SELECT tab1.name tab1_name, 
       tab2.name tab2_name 
FROM tab1 CROSS JOIN tab2;

Result:

It is ANSI SQL-92 syntax. But You may either use ANSI SQL-89 syntax:

SELECT tab1.name tab1_name, 
       tab2.name tab2_name 
FROM tab1,tab2;

Result:

The result is the same, but it is better use new ANSI standard.
The examples above return 9 records. What is happend when get all columns from these tables?

SELECT tab1.id tab1_id,tab1.name tab1_name, 
       tab2.id tab2_id, tab2.name tab2_name, 
       tab2.if_is tab2_if_is
FROM tab1 CROSS JOIN tab2;

Result:

The query returns the same numbers of records : 9.
In the same way You may either cross join data from the same table, this is named SELF CROSS JOIN.

SELECT t1.name name1, 
       t2.name name2 
FROM tab1 t1 CROSS JOIN tab1 t2;

Result:

And which will be result for three tables?
It is another example:

SELECT tab1.name tab1_name, 
       tab2.name tab2_name,
       tab3.name tab3_name  
FROM tab1 CROSS JOIN tab2
          CROSS JOIN tab3;

Result:

In this example the result of records will be 27.
Why? Because the cross join is cartesian product so numbers of records in one table multiplies numbers of records in another table. In every our three tables are three records. So for two tables 3×3 is 9, and for three tables it will be 3x3x3 = 9×3 = 27.