1

Here is my query:

select     c._id, c.cat_name 
from       category c 
inner join deptcat dc 
on         c._id != dc.cat_id 
inner join department d 
on         dc.dept_id != d._id 
where      d._id = 1

I have 3 tables department, category and deptcat:

CREATE TABLE department
  (_id, dept_name)
AS
  VALUES
    ( 1, 'CSE' ),
    ( 2, 'E&E' ) ;

CREATE TABLE category
  (_id, cat_name)
AS
  VALUES
    ( 1, 'a' ),
    ( 2, 'b' ),
    ( 3, 'c' ),
    ( 4, 'd' ) ;

CREATE TABLE deptcat
  (_id, dept_id, cat_id)
AS
  VALUES
    ( 1, 1, 1 ),
    ( 2, 1, 2 ) ;

My output should be like this:

_id | cat_name
----|-----------
3   | c
4   | d

I need to fetch the cat_id's which is not present in the deptcat table and also associated with dept_id 1, so can some one help me here?

2 Answers 2

3

You can use the exists condition to check for the existence of a record through a subquery. This is often preferable to an anti join (a left join where we make sure the right table is null) as the engine stops as soon as it finds a record.

Sample code:

select
    category._id,
    category.cat_name
from
    category
where
    not exists(
        select
            *
        from
            deptcat
            inner join department on(
                department._id = deptcat.dept_id
            )
        where
            deptcat.cat_id = category._id
            and department._id = 1
    )
4
  • 1
    Nitpicking: it's still an anti-join, whether you write it with NOT EXISTS, LEFT JOIN / IS NULL or NOT IN ;) May 15, 2017 at 9:51
  • @ypercubeᵀᴹ Is it really an anti join though? I mean, a join is defined as combining the records from 2 tables into 1 result set. A [not] exists clause is a subquery that does not return records to the result set. An anti join in the sense of left join / is null still adds additional selectable columns
    – Scoots
    May 15, 2017 at 10:46
  • 1
    select a.* from a left join b on a.aid=b.aid where b.aid is null; is the equivalent with left join. No need to add any columns from b (which will be null anyway). And yes, both are anti-joins: en.wikipedia.org/wiki/… May 15, 2017 at 14:20
  • @ypercubeᵀᴹ I was just clarifying that it was actually an anti-join, and not some other term. I am happy to accept that you had the right of it :)
    – Scoots
    May 16, 2017 at 13:16
2

To provide some insight into what was problematic in your original approach:

Joins compare all valid rows in one table to all valid rows in the other. What they do next depends on the join type:

  • Inner joins keep each pair of rows where the join conditions are met;
  • Outer joins keep all records from one table (the one on the left side of the join in a LEFT OUTER JOIN, the one on the right side in a RIGHT OUTER JOIN), and include matching rows from the other table where found (where not found, what amounts to a "dummy" row from the other table is produce, with the values for all the other table's columns NULL (even if that column is defined as NOT NULL));
  • Cross joins have no join conditions, so they keep all rows, matching rows from the tables where possible, and using a NULL row where there is no match for one table or the other.

So, look at your join. It is an inner join (usually easier to work through than an outer join). The join condition is category._id != deptcat.cat_id

So, you'd get back (just going to show the ID values of each row):

category._id      deptcat._id      deptcat.cat_id
     1                 2                  2
     2                 1                  1
     3                 1                  1
     3                 2                  2
     4                 1                  1
     4                 2                  2

This is, naturally, far more than you meant to bring back. However, then we wind up going the other direction. The join with the department table matches when department._id != deptcat.dept_id. Howeer, since all the deptcat records are for department 1, and youonly want data for department._id = 1, there are no records that match, and you wouldn't get anything back.

When trying to test something like this, the quickest way to figure problems out is usually to break it down so you're getting back just one table's data; look at that and understand what you're getting there; and then add in other tables one at a time. If the results don't make sense at first, bring back all data for the latest table, and start applying the join conditions manually. If your results don't match what the computer is giving you, then you've misunderstood something about how it's tying things together.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.