![](\"https:\/\/blog.bar-solutions.com\/img\/mailto.png\")
<\/span>\n<\/td>\n<\/p>\n
Dear Patrick,<\/p>\n
What is an ANTI-JOIN? And what is the difference between the SEMI-JOIN and the ANTI-JOIN?<\/p>\n
Lillian Sturdey
\n<\/p>\n
Dear Lillian,<\/p>\n
First of all, both SEMI-JOIN and ANTI-JOIN are not in the SQL syntax but they are more a pattern. You might expect to be able to write something like:<\/p>\n
[PATRICK]SQL>SELECT d.deptno, d.dname, d.loc\r\n FROM dept d\r\n SEMI JOIN emp e ON (e.deptno = d.deptno)\r\n \/<\/pre>\nto get all the departments that have at least one employee.
\nOr:<\/p>\n[PATRICK]SQL>SELECT d.deptno, d.dname, d.loc\r\n FROM dept d\r\n ANTI JOIN emp e ON (e.deptno = d.deptno)\r\n \/<\/pre>\nto get the departments with no employees. But all you get is an error saying your command is not properly ended, which can be read as a syntax error.<\/p>\n
ERROR at line 3:\r\nORA-00933: ORA-00933 SQL command not properly ended.<\/pre>\nMaybe your first idea would be to use a normal join to get all the departments with at least one employee:<\/p>\n
[PATRICK]SQL>SELECT d.deptno, d.dname, d.loc\r\n FROM dept d\r\n JOIN emp e ON (e.deptno = d.deptno)\r\n \/<\/pre>\nBut this results in a record for every row in the EMP table. And we only wanted every unique department.<\/p>\n
DEPTNO DNAME LOC\r\n---------- -------------- -------------\r\n 20 RESEARCH DALLAS\r\n 30 SALES CHICAGO\r\n 30 SALES CHICAGO\r\n 20 RESEARCH DALLAS\r\n 30 SALES CHICAGO\r\n 30 SALES CHICAGO\r\n 10 ACCOUNTING NEW YORK\r\n 20 RESEARCH DALLAS\r\n 10 ACCOUNTING NEW YORK\r\n 30 SALES CHICAGO\r\n 20 RESEARCH DALLAS\r\n\r\n DEPTNO DNAME LOC\r\n---------- -------------- -------------\r\n 30 SALES CHICAGO\r\n 20 RESEARCH DALLAS\r\n 10 ACCOUNTING NEW YORK\r\n\r\n14 rows selected.<\/pre>\nWell, that\u2019s easy enough, you think, just add a DISTINCT to the statement:<\/p>\n
[PATRICK]SQL>SELECT DISTINCT d.deptno, d.dname, d.loc\r\n FROM dept d\r\n JOIN emp e ON (e.deptno = d.deptno) \r\n \/<\/pre>\nExactly the result we are looking for:<\/p>\n
DEPTNO DNAME LOC\r\n---------- -------------- -------------\r\n 20 RESEARCH DALLAS\r\n 10 ACCOUNTING NEW YORK\r\n 30 SALES CHICAGO<\/pre>\nBut what if the EMP table contains hundreds, thousands or maybe millions of rows. That would mean the database has to do a lot of work to filter out the distinct values.
\nA different, and probably better, approach would be to use the SEMI-JOIN pattern. You can use the IN operator like this:<\/p>\n[PATRICK]SQL>SELECT d.deptno, d.dname, d.loc\r\n FROM dept d\r\n WHERE d.deptno IN (SELECT e.deptno\r\n FROM emp e)\r\n \/<\/pre>\nDEPTNO DNAME LOC\r\n---------- -------------- -------------\r\n 20 RESEARCH DALLAS\r\n 30 SALES CHICAGO\r\n 10 ACCOUNTING NEW YORK<\/pre>\nThis is exactly what we want to see but for big tables this is not the correct way to go. For every record in the dept table all the records in the EMP table are checked. Again, if we have a lot of employees, this means a lot of work for the database.
\nA better SEMI-JOIN to use is the EXISTS operator:<\/p>\n[PATRICK]SQL>SELECT d.deptno, d.dname, d.loc\r\n FROM dept d\r\n WHERE EXISTS (SELECT 1\r\n FROM emp e\r\n WHERE e.deptno = d.deptno)\r\n \/<\/pre>\nPlease note that with the current optimizer in the database Oracle will rewrite your query to use the best approach for the task. If the inner table (in our example EMP) is rather small, then the IN approach might be the best, in other cases it might be better to use the EXISTS approach. Where in earlier versions you had to think about which way to go (IN is better for small tables, EXISTS is better for big ones), you can now rely on the optimizer to make the correct choice.
\nIf you would want to see exactly the opposite of this query, i.e. all departments with no employees, you use an ANTI-JOIN pattern, which is pretty much the same but in this case you use NOT IN or NOT EXISTS. A different approach, which I think is pretty nice is to use an OUTER JOIN and check for the non-existence of values in column for the OUTER JOINED table.<\/p>\n[PATRICK]SQL>SELECT d.deptno, d.dname, d.loc\r\n FROM dept d\r\n LEFT OUTER JOIN emp e ON (e.deptno = d.deptno)\r\n WHERE e.empno IS NULL\r\n \/<\/pre>\nDEPTNO DNAME LOC\r\n---------- -------------- -------------\r\n 40 OPERATIONS BOSTON<\/pre>\nHope this gives you a bit more insight in this subject and gives you a better understanding of the wonders of the SQL language. Notice there are many ways to reach the same result, but one approach might be more economical than the other.<\/p>\n
Happy Oracle\u2019ing,
\nPatrick Barel<\/p>\nHope this answers your question.
\nHappy Oracle\u2019ing,
\nPatrick Barel<\/p>\n
| \n<\/span>\n<\/td>\n | \nIf you have any comments on this subject or you have a question you want answered, please send an email to patrick[at]bar-solutions[dot]com<\/a>. If I know the answer, or can find it for you, maybe I can help.<\/span>\n<\/td>\n<\/tr>\n<\/table>\n <\/p>\n |