Advice on SQL Query

Posted on 2011-05-09
Last Modified: 2012-05-11

I still trying to get some more complex SQL queries written - I can manage simpler stuff but I would like to ensure that I am writing reasonable efficient queries also.

In my database I have a table ijob - this is linked to from ijobelement which holds the elements of a job and which is itself linked to from item which defines the type of item in each element.  

I want to return a list of all jobs which do not have any job elements of type 2 and as such have produced the following query

select * from ijob where ijob_id NOT IN
(select ijob_id from ijobelement where iitem_id IN (SELECT iitem_id from iitem where iitem_type=2))

Works but I think that there must be a better way of doing this as I'm not that keen on the use of 'IN' as I suspect it may be quite slow.

I also need to extend this query so that I can check for jobs which have no job elements of type 2 but have 1 or more job elements of type 2.  How could I do that other than just adding another IN clause to the query.

Any suggestions welcome on how to improve and extend this query would be welcome!
Question by:ChrisMDrew
    LVL 32

    Accepted Solution

    select * 
    from ijob 
    inner join ijobelement on ijob.ijob_id = ijobelement.ijob_id 
    inner join iitem on iitem.iitem_id  = ijobelement.iitem_id 
    where iitem_type<>2

    Open in new window

    LVL 12

    Assisted Solution

    NOT EXISTS is an alternaitive to NOT IN

    Select * from ijob j
    where NOT EXISTS
    ( Select 1 from ijobelelment e
      where e.ijob_id = j.ijob_id
      and iitem_id  in (SELECT iitem_id from iitem where iitem_type=2)

    Everyone always advises against NOT IN because of the posiblity of full table scans.   However,  I don't always find it is the slowest method.    I think optimisers have a big part to play in this.   Try alternative syntaxes and see which work for you.

    LVL 40

    Assisted Solution

    Check this.
    SELECT t1.* 
      FROM ijob t1 
           LEFT JOIN (SELECT t2.ijob_id 
                        FROM ijobelement t2 
                             JOIN iitem t3 
                               ON t2.iitem_id = t3.iitem_id 
                       WHERE t3.iitem_type = 2) t4 
             ON t1.ijob_id = t4.ijob_id 
     WHERE t4.ijob_id IS NULL

    Open in new window


    Author Closing Comment

    Thanks for the help guys - all of these suggestions gave me some useful ideas and have helped me to get a query which works quickly!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Suggested Solutions

    I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
    In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now