what is an INNER JOIN in SQL SERVER

 

            INNER JOIN in SQL SERVER

In this tutorial, you will see the complete steps to create a Inner Join table in SQL Server Management Studio 2019 (SSMS)

You will also learn how to create a table using only Queries also.

step to create a table in SQL SERVER MANAGEMENT STUDIO (2019)

Step 1.create a database 

If you haven't create already so, create a database in SQL Server Management Studio. For illustration purposes, a database called sqlinner was created:

or Using to Create SQL query Method:

Create database sqlinner;

Use sqlinner;

Step 2: Create a table

Under your database (for example sqlinner), right-click on the Tables folder, and then select Table. 

from the drop-down list: 

You will  be able to create your table. For example, let’s say that you want to create a table called brand. This table will have 4 columns:

• brandId
• Name
• Brand
• Available

or Using  SQL query method:

Create table brand (brandId int  primary key identity(1,1) not null,name varchar(20) not null,brand varchar(20) not null,Available varchar(20) not null);

same way create table 2:

• brandId
• Price

or Using  SQL query method:

Create table brandprice(brandId int primary key identity(1,1)not null,price varchar(20) not null);

Let’s  select the brandId as the Identity Column. This will ensure that an auto-increment is applied to the brandId column whenever a new record is inserted into the table an automatically number counter. 

Notice that the brandId column is set to be the Primary Key with an auto-increment by adding identity(1,1) primary key to the above query.

And then Insert the data on Table using Queries:

Brand:

Insert into brand (name,brand,Available)values('shirt','addias','yes');

Insert into brand (name,brand,Available)values('shoes','woodland','yes');

Insert into brand (name,brand,Available)values('shirt','woodland','No');

Insert into brand (name,brand,Available)values('shoes','Bata','yes');

Insert into brand (name,brand,Available)values('shirt','peter england','yes');

Insert into brand (name,brand,Available)values('shirt','otto','yes');

Insert into brand (name,brand,Available)values('shirt','weis','No');

Select * from brand: -> Execute the command or F5

BrandPrice:

Insert into brandprice(price)values(500);

Insert into brandprice(price)values(1500);

Insert into brandprice(price)values(200);

Insert into brandprice(price)values(700);

Insert into brandprice(price)values(200);

Insert into brandprice(price)values(500);

Insert into brandprice(price)values(2500);

Select * from brandprice; Execute the command or F5;

And then We Enter into an SQL JOIN 

SQL Join (Inner, Left, Right and Full Joins)

A SQL Join statement is used to combine data or rows from two or more tables based on a common field between them. There are different  types of Joins are:

•     INNER JOIN
•     LEFT JOIN
•     RIGHT JOIN
•     FULL JOIN

The simplest Join is INNER JOIN.

INNER JOIN: 

First we will see the  INNER JOIN and other will be later. INNER JOIN keyword are selects all rows from both the tables as long as the condition satisfies. This keyword will create the result-set by combining all rows from both the tables where the condition satisfies therefore value of the common field will be same.

Syntax:-

SELECT table1.column1,table1.column2,table2.column1,

FROM table1 

INNER JOIN table2

ON table1.match_column = table2.match_column;

INNER JOIN:

Select brand.brandId,brand.name,brand.brand,brand.available ,brandprice.brandId from brand Inner join brandprice on brand.brandId = brandprice.brandId;

Execute the command or press F5;

Output:-