INNER JOIN in T-SQL in SQL Server 2012

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

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

use mybase;

create table tab10 
(
	id int primary key,
	name varchar(100)
);

create table tab20 
(
	id int primary key,
	name varchar(100) ,
	if_is bit
);

create table tab30
(
	id int primary key,
	name varchar(100) 
);

insert into tab10 values(1,'one');
insert into tab10 values(2,'two');
insert into tab10 values(3,'three');
insert into tab10 values(4,'four');
insert into tab10 values(5,null);

insert into tab20 values(1,'one',0);
insert into tab20 values(2,'two',1);
insert into tab20 values(3,'three',1);
insert into tab20 values(4,'cztery',1);
insert into tab20 values(5,null,0);

insert into tab30 values(1,'one');
insert into tab30 values(2,'two');
insert into tab30 values(3,'three');
insert into tab30 values(4,null);
insert into tab30 values(5,null);

When You get all data from these tables You see:

SELECT * FROM tab10;
SELECT * FROM tab20;
SELECT * FROM tab30;

z1

The INNER JOIN returns records from tables when the given columns in both tables are matches.
The use INNER JOIN is the same as use JOIN.
Below example show You how work inner join for tab10 and tab20 for condition that id in one table is equal id in second table:

SELECT tab10.name  tab10_name, 
       tab20.name tab20_name
FROM tab10 
JOIN tab20 
ON tab10.id = tab20.id;

Result:
z2

And how result will be for comparise the name columns?

SELECT tab10.id id_tab10,  tab10.name name_tab10, 
       tab20.id id_tab20,  tab20.name name_tab20 
FROM tab10 
JOIN tab20 
ON tab10.name = tab20.name;

Result:
z3As You may notice the NULL value in name column is not the same for equal operator.

The comparison may not be equal. It shows example below:

SELECT tab10.id id_tab10,  tab10.name name_tab10, 
       tab20.id id_tab20,  tab20.name name_tab20 
FROM tab10 
JOIN tab20 
ON tab10.name != tab20.name;

Result:
z4

As You may notice the NULL value in name column is not taken as data for not equal operator, either.

And another example:

SELECT tab10.name tab10_name, 
       tab20.name tab20_name
FROM tab10 
JOIN tab20 
ON tab10.id > tab20.id;

Result:
z5
And other example:

SELECT tab10.id tab10_id, tab10.name  tab10_name, 
       tab20.id tab20_id,tab20.name tab20_name
FROM tab10 
JOIN tab20 
ON tab10.id > tab20.id;

Result:
z6
The below example shows how use complex condition with AND and IS NOT NULL operator:

SELECT tab10.id tab10_id, tab10.name  tab10_name, 
       tab20.id tab20_id,tab20.name tab20_name
FROM tab10 
JOIN tab20 
ON tab10.id > tab20.id 
   AND tab10.name IS NOT NULL;

Result:
z7

Leave a Reply

Your email address will not be published.

Time limit is exhausted. Please reload CAPTCHA.