Learn Microsoft Sql Server: November 2017

CREATE DATABASE demo_oueter_join

go

USE demo_oueter_join

go

CREATE TABLE company
(
companyid INT IDENTITY(1, 1),
NAME VARCHAR(100)
)

CREATE TABLE department
(
departmentid INT IDENTITY(1, 1),
NAME VARCHAR(100)
)

CREATE TABLE employeedetail
(
employeeid INT IDENTITY(1, 1),
employeename VARCHAR(100),
departmentid INT,
companyid INT
)

go

SELECT *
FROM company

SELECT *
FROM department

SELECT *
FROM employeedetail

go

INSERT INTO company
(NAME)
SELECT 'A'
UNION
SELECT 'B'

INSERT INTO department
(NAME)
SELECT 'X'
UNION
SELECT 'Y'

INSERT INTO employeedetail
(employeename,
departmentid,
companyid)
SELECT 'Paul',
1,
3
UNION ALL
SELECT 'Sam',
2,
2

go

--Inner join :
--select * from
--
--
--
--on
SELECT emp.employeeid,
emp.employeename,
Cmp.NAME [companyName],
dept.NAME [Department Name]
FROM employeedetail emp
INNER JOIN company cmp
ON emp.companyid = cmp.companyid

create database test_demo1

go

use test_demo1

go

create table Company (companyId int identity(1,1),Name varchar(100))

create table Department (departmentId int identity(1,1),name varchar(100))

create table EmployeeDetail (employeeId int identity(1,1),employeeName varchar(100), departmentId int ,companyId int)

go

select * from Company

select * from Department

select * From EmployeeDetail

go

insert into Company(Name)

select 'A'

union

select 'B'

insert into Department(Name)

select 'X'

union

select 'Y'

insert into EmployeeDetail

(

employeeName,

departmentId,

companyId)

select 'Shrikant',1,1

union all

select 'Sam',2,2

go

--Inner join :

--select * from

--<tab1 name> <alias>

--<join type >

--<tab2 name> <alias>

--on <condition>

select emp.employeeId,emp.employeeName,Cmp.Name [companyName] ,dept.name [Department Name] from

EmployeeDetail emp

inner join

Company cmp

on emp.companyId=cmp.companyId

inner join

Department dept

on emp.departmentId=dept.departmentId