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