February 9, 2015

SQL Join Query Tips-Example of right ,self query itselft,anti ,left join,Full outer join


Example of self join query in sql.

Summary :

If you are looking for information like self join query mean table used self as join let us see following example to get more clear.

Hear I used  right join with self join.

Example of self join query in sql,right join,self join,join query itself.

select * from emp where empno not in (select e.empno from emp e right join dept d on e.empno = d.empno)

How to use self join in sql select query

Example of self join:

To find the IDs which have a different department:

select distinct A.id  from myTable A join myTable B on A.id = B.id
where A.dept <> B.dept

Example of join select query in sql

Query to get a list of all students who have a certain checklist item (MFST) with a status of Initiated where all other checklist items have a status of Completed. Should I be using Exists or should I be using someting else?

SELECT A.COMMON_ID, 
       B.AID_YEAR, 
       C.CHKLST_ITEM_CD, 
       C.ITEM_STATUS
FROM PS_PERSON_CHECKLST A
JOIN PS_VAR_DATA_FINA B   ON (B.VAR_DATA_SEQ = A.VAR_DATA_SEQ AND B.COMMON_ID = A.COMMON_ID)
JOIN PS_PERSON_CHK_ITEM C ON (C.SEQ_3C = A.SEQ_3C AND C.COMMON_ID = A.COMMON_ID)
WHERE A.INSTITUTION = :1
  AND B.AID_YEAR = '2013'
  AND (C.CHKLST_ITEM_CD LIKE 'MFST%' AND C.ITEM_STATUS = 'I')
   OR (C2.CHKLST_ITEM_CD NOT LIKE 'MFST%' AND C2.ITEM_STATUS = 'C');

Example of left join in sql

This is simple Example of left join in sql. You can try with your tables to see result of query.

select stuff  from folks 
join (select fkey from these events ) yummy on folks.key = yummy .fkey 
left join (select fkey from those events) yuck on folks.key = yuck.fkey 
where yuck.fkey is null 

Anti join operation and left join in sql

 The NOT IN operator (an anti-join operator) is more efficient than a LEFT JOIN...IS NULL construction because:

 The LEFT JOIN produces a row for each left-side row, extending it as needed with NULL value placeholders. The resulting row is then evaluated for the NULL value.

 The NOT IN operator performs an equi-join and produces a result row only when the equi-join fails. There is no post-join predicate to evaluate.

 The cost of the LEFT JOIN plan includes the join and (the predicate evaluation X left-side rows). The cost of the NOT IN plan is the join alone.

Example of full outer join in sql

This is Example of full outer join in sql

select
isnull(c.CPTCode ,p.CPTCode) as CPTCode
, c.mtd
, p.TotalCharges
, p.TotalUnits
from #C c full outer join #Prod p
on c.grpType = p.grpType
and c.CPTCode = p.CPTCode

Which produced the following result

CPTCode mtd TotalCharges TotalUnits
76800 1321.61 6100.00 12
76856 246.01 NULL NULL
76881 9778.95 38749.00 81
76942 22467.33 54733.00 114
76880 NULL -475.00 -1