Hi all,

I"m trying to do a "not-equal" join... basically, I have 2 tables, table A and B. I'm trying to select rows in table A that are NOT in table B based on a "not equal" comoparison on a field.

Here's my query

select * from location, bannedlocation where (location.id != bannedlocation.id)

It's not working.. can anyone give me a hand? Thanks.

    select * from location where id not in (select id from bannedlocation)

      5 days later

      or, if you mysql version does not support subselects yet, it would be as explained in the manual:

      If there is no matching record for the right table in the ON or USING part in a LEFT JOIN, a row with all columns set to NULL is used for the right table. You can use this fact to find records in a table that have no counterpart in another table:

      mysql> SELECT table1.* FROM table1
    ->        LEFT JOIN table2 ON table1.id=table2.id
    ->        WHERE table2.id IS NULL;

      (see http://dev.mysql.com/doc/mysql/en/join.html)

        xblue's solution is the correct one - and the standard solution to this situation as the quote from the manual shows. It bypasses any problems over sub-query support and would be fully portable across every sql db engine.

          2 years later

          Hi I am facing an issue,

          I am having two table T1 and T2, I need to get the result of all the records from T2which are not mched in table1. similer like

          Select T1.* from T1
          where T1.Id NOT IN ( Select T2.Id from T2)

          This is taking a long time to execute.
          TAble 1 has around 20000 records

          Also these two queries taking more time ...

          SELECT DISTINCT T1.NO FROM TABLE1 T1
          LEFT JOIN TABLE2 T2 ON T1.NO = T2.NO

          SELECT DISTINCT T1.NO FROM TABLE1 T1
          LEFT JOIN TABLE2 T2 ON T1.NO <> T2.NO

          Can any one tell me how to reduce the responds time when we use not equal in query - MY SQL

            Are the columns used to join the tables indexed? If not then index them and that will speed things up.

            Use EXPLAIN to see what is going on with the slow queries.

              Hi

              I am new to MY SQL so how to use EXPLAIN, is it just bofore the query which we have to right ?

              The tables which i using are indexed. The thing is , the <> is taking too much time. If i use '=' instead of '<>' then the result is too fast else it will take around 4minute.

              Can you please explain ?

                To use EXPLAIN, just add it to the front of your query and then run, i.e.

                EXPLAIN SELECT DISTINCT T1.NO FROM TABLE1 T1
LEFT JOIN TABLE2 T2 ON T1.NO <> T2.NO

                MySQL will not return the query results but will return information about how it's using the indexes to join the tables in the query.

                The reason why your second query is taking so long is that it's matching the wrong thing. Consider these tables.

                table 1

                1
                2
                3
                4
                5

                table 2

                3
                4

                Your query will produce these results
                t1.id t2.id
                1 3
                1 4
                2 3
                2 4
                3 4
                4 3
                5 3
                5 4
                which is probably not what you want.
                If you use this query:

                SELECT DISTINCT T1.NO FROM TABLE1 T1
LEFT JOIN TABLE2 T2 USING (NO) WHERE T2.NO IS NULL

                you will get these results
                t1.id t2.id
                1 NULL
                2 NULL
                5 NULL
                i.e. all those rows in t1 which are not in t2.

                  Write a Reply...