NOT IN Syntax

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • antony14
    New Member
    • Sep 2007
    • 1

    #1

    NOT IN Syntax

    i was using "NOT IN" syntax to get the details from a table1 which is not in table2, but it shows nothing when the table2 is empty, can i get all the details from table1 even if the table2 is empty.
    [code=mysql]
    select * from table1, table2 where table1.ID NOT IN table2.ID,[/code]
    Last edited by pbmods; Sep 12 '07, 11:00 PM. Reason: Added CODE tags.
  • amitpatel66
    Recognized Expert Top Contributor
    • Mar 2007
    • 2358

    #2
    Originally posted by antony14
    i was using "NOT IN" syntax to get the details from a table1 which is not in table2, but it shows nothing when the table2 is empty, can i get all the details from table1 even if the table2 is empty.

    select * from table1, table2 where table1.ID NOT IN table2.ID,
    Try below Query:

    [code=oracle]
    SELECT * from table1 WHERE id NOT IN (SELECT id FROM table2)
    [/code]

    Comment

    • mwasif
      Recognized Expert Contributor
      • Jul 2006
      • 802

      #3
      Try this one
      [CODE=mysql]SELECT table1.* FROM table1
      LEFT JOIN table2 ON table1.ID = table2.ID
      WHERE table2.ID IS NULL[/CODE]

      Comment

      • pbmods
        Recognized Expert Expert
        • Apr 2007
        • 5821

        #4
        Or even:
        [code=mysql]
        SELECT
        *
        FROM
        `table1`
        WHERE
        NOT
        EXISTS
        (
        SELECT
        *
        FROM
        `table2`
        WHERE
        `id` = `table1`.`id`
        )
        [/code]

        PS. Please use CODE tags when posting source code:

        [CODE=My SQL]
        MySQL code goes here.
        [/CODE]

        Comment

        Working...