Delete duplicate values by using CTE

How to Delete Duplicate values using CTE ?
CREATE TABLE [dbo].[emp]
(
[id] [int]  NULL,
[name] [varchar](20) NULL,
[city] [varchar] (20) NULL,
[dept] [varchar](10) NULL,
[salary] [money] NULL

)

insert into emp values(100,'Kishore','bangalore','IT',48000)
insert into emp values(101,'pavan','bangalore','HR',32000)
insert into emp values(102,'mahesh','pune','Slaes',24000)
insert into emp values(103,'harish','mumbai','IT',42000)
insert into emp values(104,'ramya','pune','HR',28000)

insert into emp values(105,'swetha','bangalore','Slaes', 26000)
insert into emp values(102,'mahesh','pune','Slaes',24000)
insert into emp values(103,'harish','mumbai','IT',42000)
insert into emp values(104,'ramya','pune','HR',28000)

Actual Table:

Required Result:

Query:
with e(edup)
As
(
             select id,
             ROW_NUMBER() over(PARTITION by id,name,city                      order by id ) as edup 
             from emp
)

delete from e where edup>1

Result:(3 row(s) affected)

We can also delete duplicates by using temporary table

1. Selete distinct * into #emp123 from dbo.emp
2. Truncate table dbo.emp
3. Insert into Emp Select * from #emp123

Joins

                                                             JOINS

• It is a process of joining column from 2 or more tables that always produces a new table.
• joining condition is must 
• To join n tables 'N-1' joining conditions required.
• A joining condition can be followed by any no of filtering conditions.

There are 6 types are there....

1. Equi join
2. Non-Equi join
3. Left Outer join
4. Right Outer join
5. Cartesian join
6. Self join

Sample tables:

create table tab1
(
id int,
name varchar(20),
salary money
)

create table tab2
(
id int,
did int
)

insert into tab1 values(1,'A',2000),(2,'P',3000),(5,'J',2500),(8,'AK',5000)

insert into tab2 values(1,26),(2,50),(3,35),(4,56),(6,25)

1.Equi join:

                 At This join concept columns from 2 or more tables can be joined by making a joining condition using '=' operation.

                  To work with Equi join, Table should contain at least one column with respect to data and data type.

Ex:

Select * from tab1,tab2 where tab1.id = tab2.id

2.Non-Equi join:

                       At This join concept columns from 2 or more tables can be joined by making a joining condition without using '=' operation.

                  To work with Non-Equi join, Table should contain at least one column with respect to data and data type.

Ex:

Select * from tab1,tab2 where tab1.id > tab2.id

3.Outer join:(Right /Left Outer joins)

                     At this join concept columns from 2 or more tables can be joins with respect to rows that gets matched and unmatched with joining conditions.

                      Sql Server provides outer join operator as '*' which should be specified at left / right side of joining condition.

There are 2 types of joins.

1. Left Outer Join
2. Right Outer Join

• Left Outer Join:

                           At this joining concept outer join operator is placed on left side of joining condition,which will retrieve match and unmatched rows from left side table which is on left side of the joining condition and substitutes null values for the columns of right side table.

Ex:

Select * from tab1,tab2 where tab1.id * = tab2.id

• Right Outer Join:

                           At this joining concept outer join operator is placed on right side of joining condition,which will retrieve match and unmatched rows from right side table which is on right side of the joining condition and substitutes null values for the columns of left side table.

Ex:

Select * from tab1,tab2 where tab1.id  = * tab2.id

4.Cartesian join:

                          This join concept supports to join the columns from 2 or more tables with out any joining condition.

                          It provides multiplication of rows among the tables used for joins.

Ex:

Select * from tab1,tab2

we will get (4X5=) 20 rows.

5.Self Join:

                         At this join concept single table will be used for more than once, Differentiating them with alias names.

                          Data is retrieved, joining condition is made on single table .

Ex:

select t1.id,t2.id from tab1 t1 , tab t2

where t1.id=t2.id