473,468 Members | 1,329 Online
Bytes | Software Development & Data Engineering Community
Create Post

Home Posts Topics Members FAQ

NOT IN Syntax

1 New Member
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.
Expand|Select|Wrap|Line Numbers
  1. select * from table1, table2 where table1.ID NOT IN table2.ID,
Sep 12 '07 #1
3 5967
amitpatel66
2,367 Recognized Expert Top Contributor
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT * from table1 WHERE id NOT IN (SELECT id FROM table2)
  2.  
Sep 12 '07 #2
mwasif
802 Recognized Expert Contributor
Try this one
Expand|Select|Wrap|Line Numbers
  1. SELECT table1.* FROM table1 
  2. LEFT JOIN table2 ON table1.ID = table2.ID
  3. WHERE table2.ID IS NULL
Sep 12 '07 #3
pbmods
5,821 Recognized Expert Expert
Or even:
Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.         *
  3.     FROM
  4.         `table1`
  5.     WHERE
  6.         NOT
  7.         EXISTS
  8.         (
  9.             SELECT
  10.                     *
  11.                 FROM
  12.                     `table2`
  13.                 WHERE
  14.                     `id` = `table1`.`id`
  15.         )
  16.  
PS. Please use CODE tags when posting source code:

[CODE=MySQL]
MySQL code goes here.
[/CODE]
Sep 12 '07 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

699
by: mike420 | last post by:
I think everyone who used Python will agree that its syntax is the best thing going for it. It is very readable and easy for everyone to learn. But, Python does not a have very good macro...
22
by: Tuang | last post by:
I'm checking out Python as a candidate for replacing Perl as my "Swiss Army knife" tool. The longer I can remember the syntax for performing a task, the more likely I am to use it on the spot if...
14
by: Sandy Norton | last post by:
If we are going to be stuck with @decorators for 2.4, then how about using blocks and indentation to elminate repetition and increase readability: Example 1 --------- class Klass: def...
16
by: George Sakkis | last post by:
I'm sure there must have been a past thread about this topic but I don't know how to find it: How about extending the "for <X> in" syntax so that X can include default arguments ? This would be very...
23
by: Carter Smith | last post by:
http://www.icarusindie.com/Literature/ebooks/ Rather than advocating wasting money on expensive books for beginners, here's my collection of ebooks that have been made freely available on-line...
19
by: Nicolas Fleury | last post by:
Hi everyone, I would to know what do you think of this PEP. Any comment welcomed (even about English mistakes). PEP: XXX Title: Specialization Syntax Version: $Revision: 1.10 $...
4
by: Jeremy Yallop | last post by:
Looking over some code I came across a line like this if isalnum((unsigned char)c) { which was accepted by the compiler without complaint. Should the compiler have issued a diagnostic in this...
177
by: C# Learner | last post by:
Why is C syntax so uneasy on the eye? In its day, was it _really_ designed by snobby programmers to scare away potential "n00bs"? If so, and after 50+ years of programming research, why are...
4
by: Bob hotmail.com> | last post by:
Everyone I have been spending weeks looking on the web for a good tutorial on how to use regular expressions and other methods to satisfy my craving for learning how to do FAST c-style syntax...
3
by: Manuel | last post by:
I'm trying to compile glut 3.7.6 (dowbloaded from official site)using devc++. So I've imported the glut32.dsp into devc++, included manually some headers, and start to compile. It return a very...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
1
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
0
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
0
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.