How to use Left outer  join in SQL Database Server




left outer join in sql




In this tutorial, you will see the complete steps to create or use  a Left outer Join table in SQL Database 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:


left outer join




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: 


left outer join




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:


left outer join




  • 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


left outer join



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;


left outer join




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 ----> preview tutorial Inner join Link
  •     LEFT JOIN
  •     RIGHT JOIN
  •     FULL JOIN


The simplest Join is LEFT OUTER  JOIN.

LEFT JOIN: Left  join returns all the rows of the table on the left side  and matching rows for the table on the right side of join.  LEFT JOIN is also called  as LEFT OUTER JOIN



Syntax:

SELECT table1.column1,table1.column2,table2.column1
FROM table1 
LEFT JOIN table2
ON table1.match_column = table2.match_column;


LEFT JOIN:-



left outer join





select brand.brandId , brand.name,brandprice.price from brand left join brandprice on brand.brandId=brandprice.brandId;


left outer join




Execute the command or F5;


Output:-

left outer join





Related Links:

Previous Post Next Post