select statement with sub query

Posted on 2005-05-02
Last Modified: 2012-05-05
I have what look like a very simple select query statement
with one sub query, but for some reason, it takes a long
time to come up (1+ min).

select empid, lname, fname from table1
where empid not in (select empid from table2 where HireDate = '01/1/2004')
order by empid.

How can I fix this query so that it would be better or more optimized?

** when I run the sub query on QA, it returns like 10 records, could that
be the cause?
Question by:graphix03
    LVL 65

    Expert Comment

    by:Jim Horn
    How about...

    select empid, lname, fname
    from table1
    inner join table2 on table1.SomeID = table2.SomeID
    where table2.HireDate = '01/1/2004'
    order by table1.empid

    Author Comment

    But will it do the part I asked for which is where empid in table1 NOT IN table2?
    LVL 65

    Accepted Solution

    ... oops, forgot to switch signs around ...

    where table2.HireDate <> '01/1/2004'

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    729 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

    15 Experts available now in Live!

    Get 1:1 Help Now