473,706 Members | 2,441 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Negative SELECT in mysql?

How does one retrieve the rows in a select statement that DONT match the
select.

select CarIndex FROM DealerCatalog, BigCatalog WHERE
DealerCatalog.C arIndex=BigCata log.CarIndex

finds all the cars in the dealer catalog that are in the bigger distributor
catalog.

How do I do the opposite in a single sql statement i.e. all the dealer cars
that AREN'T in the big distributor catalog?

Is there a negative Select?

Jul 17 '05 #1
12 14058
kaptain kernel wrote:

How does one retrieve the rows in a select statement that DONT match the
select.

select CarIndex FROM DealerCatalog, BigCatalog WHERE
DealerCatalog.C arIndex=BigCata log.CarIndex

finds all the cars in the dealer catalog that are in the bigger distributor
catalog.

How do I do the opposite in a single sql statement i.e. all the dealer cars
that AREN'T in the big distributor catalog?

Is there a negative Select?


select CarIndex FROM DealerCatalog, BigCatalog WHERE
DealerCatalog.C arIndex<>BigCat alog.CarIndex

Regards,
Shawn
--
Shawn Wilson
sh***@glassgian t.com
http://www.glassgiant.com

I have a spam filter. Please include "PHP" in the
subject line to ensure I'll get your message.
Jul 17 '05 #2
On 2004-01-13, kaptain kernel <no****@nospam. gov> wrote:
How does one retrieve the rows in a select statement that DONT match the
select.

select CarIndex FROM DealerCatalog, BigCatalog WHERE
DealerCatalog.C arIndex=BigCata log.CarIndex

finds all the cars in the dealer catalog that are in the bigger distributor
catalog.

How do I do the opposite in a single sql statement i.e. all the dealer cars
that AREN'T in the big distributor catalog?

Is there a negative Select?


Lookup the section about the WHERE clause in your database manual.

In this case it's enough to reverse the condition.
More general you might use the not in with subquery construct.

--
http://home.mysth.be/~timvw
Jul 17 '05 #3
Tim Van Wassenhove wrote:
On 2004-01-13, kaptain kernel <no****@nospam. gov> wrote:
How does one retrieve the rows in a select statement that DONT match the
select.

select CarIndex FROM DealerCatalog, BigCatalog WHERE
DealerCatalog .CarIndex=BigCa talog.CarIndex

finds all the cars in the dealer catalog that are in the bigger distributor
catalog.

How do I do the opposite in a single sql statement i.e. all the dealer cars
that AREN'T in the big distributor catalog?

Is there a negative Select?

Lookup the section about the WHERE clause in your database manual.

In this case it's enough to reverse the condition.
More general you might use the not in with subquery construct.

The suggestions mentioned above are certainly not speedy - I had to kill
a sql query when i changed the = in my original statement to <> as
suggested above. Which tells me that the suggestions are wrong.

The answer is to use LEFT JOIN - anything that doesn't join is given a
NULL value , and it's a heck of a lot speedier of large datasets (i've
got 12,000 records):

SELECT CarIndex FROM DealerCatalog LEFT JOIN BigCatalog ON
DealerCatalog.C arIndex=BigCata log.CarIndex WHERE BigCatalog.CarI ndex IS NULL

Jul 17 '05 #4
kaptain kernel wrote:
Tim Van Wassenhove wrote:
On 2004-01-13, kaptain kernel <no****@nospam. gov> wrote:
How does one retrieve the rows in a select statement that DONT
match the select.

select CarIndex FROM DealerCatalog, BigCatalog WHERE
DealerCatalog.C arIndex=BigCata log.CarIndex

finds all the cars in the dealer catalog that are in the bigger
distributor catalog.

How do I do the opposite in a single sql statement i.e. all the
dealer cars that AREN'T in the big distributor catalog?

Is there a negative Select?

Lookup the section about the WHERE clause in your database manual.

In this case it's enough to reverse the condition.
More general you might use the not in with subquery construct.

The suggestions mentioned above are certainly not speedy - I had to
kill a sql query when i changed the = in my original statement to
<> as
suggested above. Which tells me that the suggestions are wrong.


No kidding. You asked for a cartesian product by specifying a join of two
tables with essentially no join condition...

The answer is to use LEFT JOIN - anything that doesn't join is given a
NULL value , and it's a heck of a lot speedier of large datasets (i've
got 12,000 records):

SELECT CarIndex FROM DealerCatalog LEFT JOIN BigCatalog ON
DealerCatalog.C arIndex=BigCata log.CarIndex WHERE BigCatalog.CarI ndex
IS NULL


Actually, this is definitely not the optimal way to do it, as you are doing
a join but not using any information from the second relation. This is going
to be rather slow. Instead, do:

SELECT CarIndex FROM DealerCatalog WHERE DealerCatalog.C arIndex NOT IN
(SELECT CarIndex FROM BigCatalog);

Additionally, you should make sure that there is an index on
DealerCatalog.C arIndex and also an index on BigCatalog.CarI ndex.

-Ian
Jul 17 '05 #5
or

SELECT CarIndex FROM DealerCatalog WHERE NOT EXISTS
(SELECT * FROM BigCatalog WHERE DealerCatalog.C arIndex =
BigCatalog.CarI ndex);

Uzytkownik "Agelmar" <if**********@c omcast.net> napisal w wiadomosci
news:bu******** ****@ID-30799.news.uni-berlin.de...
Actually, this is definitely not the optimal way to do it, as you are doing a join but not using any information from the second relation. This is going to be rather slow. Instead, do:

SELECT CarIndex FROM DealerCatalog WHERE DealerCatalog.C arIndex NOT IN
(SELECT CarIndex FROM BigCatalog);

Additionally, you should make sure that there is an index on
DealerCatalog.C arIndex and also an index on BigCatalog.CarI ndex.

-Ian

Jul 17 '05 #6
Chung Leong wrote:
or

SELECT CarIndex FROM DealerCatalog WHERE NOT EXISTS
(SELECT * FROM BigCatalog WHERE DealerCatalog.C arIndex =
BigCatalog.CarI ndex);


No, that is much slower. Your method will result in a sub-query on the
database for each CarIndex in DealerCatalog. My method results in the
subquery being evaluated only once, as it is not a correlated subquery. The
database retrieves a list of CarIndex tuples from the BigCatalog relation,
and it does this only once, storing this in memory. It then probes this list
for each CarIndex in DealerCatalog. With your method, it takes CarIndex
values from DealerCatalog one at a time, and for each such value, it issues
a query against BigCatalog. Not ideal.
Jul 17 '05 #7
kaptain kernel wrote:
Tim Van Wassenhove wrote:
On 2004-01-13, kaptain kernel <no****@nospam. gov> wrote:
How does one retrieve the rows in a select statement that DONT match the
select.
[snip[
How do I do the opposite in a single sql statement i.e. all the
dealer cars
that AREN'T in the big distributor catalog?

Is there a negative Select?

[snip]
The answer is to use LEFT JOIN - anything that doesn't join is given a
NULL value , and it's a heck of a lot speedier of large datasets (i've
got 12,000 records):

SELECT CarIndex FROM DealerCatalog LEFT JOIN BigCatalog ON
DealerCatalog.C arIndex=BigCata log.CarIndex WHERE BigCatalog.CarI ndex IS
NULL


If your RDBMS vendor implemented the SET operators using the
MINUS operator may prove to be much faster; e.g.

SELECT CarIndex FROM DealerCatalog
MINUS
SELECT CarIndex FROM BigCatalog
ORDER BY 1;

Jul 17 '05 #8
kaptain kernel wrote:

Tim Van Wassenhove wrote:
On 2004-01-13, kaptain kernel <no****@nospam. gov> wrote:
How does one retrieve the rows in a select statement that DONT match the
select.

select CarIndex FROM DealerCatalog, BigCatalog WHERE
DealerCatalog .CarIndex=BigCa talog.CarIndex

finds all the cars in the dealer catalog that are in the bigger distributor
catalog.

How do I do the opposite in a single sql statement i.e. all the dealer cars
that AREN'T in the big distributor catalog?

Is there a negative Select?

Lookup the section about the WHERE clause in your database manual.

In this case it's enough to reverse the condition.
More general you might use the not in with subquery construct.


The suggestions mentioned above are certainly not speedy - I had to kill
a sql query when i changed the = in my original statement to <> as
suggested above. Which tells me that the suggestions are wrong.

The answer is to use LEFT JOIN - anything that doesn't join is given a
NULL value , and it's a heck of a lot speedier of large datasets (i've
got 12,000 records):

SELECT CarIndex FROM DealerCatalog LEFT JOIN BigCatalog ON
DealerCatalog.C arIndex=BigCata log.CarIndex WHERE BigCatalog.CarI ndex IS NULL


My apologies - I didn't notice there 2 tables. I was thinking of just a select
from a single table.

Regards,
Shawn
--
Shawn Wilson
sh***@glassgian t.com
http://www.glassgiant.com

I have a spam filter. Please include "PHP" in the
subject line to ensure I'll get your message.
Jul 17 '05 #9
Depends on the database software. On MS SQLServer using EXISTS is much
faster than using IN, rather counterintuitiv ely.

Uzytkownik "Agelmar" <if**********@c omcast.net> napisal w wiadomosci
news:bu******** ****@ID-30799.news.uni-berlin.de...
Chung Leong wrote:
or

SELECT CarIndex FROM DealerCatalog WHERE NOT EXISTS
(SELECT * FROM BigCatalog WHERE DealerCatalog.C arIndex =
BigCatalog.CarI ndex);
No, that is much slower. Your method will result in a sub-query on the
database for each CarIndex in DealerCatalog. My method results in the
subquery being evaluated only once, as it is not a correlated subquery.

The database retrieves a list of CarIndex tuples from the BigCatalog relation,
and it does this only once, storing this in memory. It then probes this list for each CarIndex in DealerCatalog. With your method, it takes CarIndex
values from DealerCatalog one at a time, and for each such value, it issues a query against BigCatalog. Not ideal.

Jul 17 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

12
3676
by: Rick | last post by:
I need to store a date prior to the unix epoch, any pointers? -- Rick Digital Printing www.intelligence-direct.com
21
5250
by: John Fabiani | last post by:
Hi, I'm a newbie and I'm attempting to learn howto create a select statement. When I use >>> string1='18 Tadlock Place' >>> cursor.execute("SELECT * FROM mytest where address = %s",string1) All works as expected. But >>> numb=10 >>> cursor.execute("SELECT * FROM mytest where clientID = %d",numb) Traceback (innermost last): File "<stdin>", line 1, in ?
0
4994
by: Fatt Shin | last post by:
Hi, I'm running MySQL 4.0.13, connecting from PowerBuilder 9 using ODCB Connector 3.51. I'm facing a problem where whenever I issue a SELECT COUNT(*) statement from PowerBuilder, I always get SQL syntax error back from MySQL. (Refer to ODBC Trace I captured below). metrohouse af8-b94 ENTER SQLExecDirect HSTMT 014D2360 UCHAR * 0x020A0EA2 "select count ( *) from code
0
3616
by: Hans Maurer | last post by:
>Description: We're running our current TTS application with MySQL (on Unix). All database, table and column names are in lower-case. However, we need to access this database with a new application which (for some reason) converts all table and column names in its SELECT statements to uppercase. Since MySQL is case-sensitive on Unix, I added the statement set-variable = lower_case_table_names=1 to /etc/my.cnf. However, statements...
2
4939
by: Richard van Denzel | last post by:
Hi All, I've defined a table (m) with an double field (bedrag) in it and when I execute the next statement: INSERT INTO m (bedrag) VALUES('-7000,00'); the value get stored correctly. I've written a PHP-script which does the following:
5
3843
by: jayson_13 | last post by:
Hi, I need to implement a counter and i face problem of locking so hope that u guys can help me. I try to do test like this : 1st connection SELECT * FROM nextkey WHERE tblname = 'PLCN' FOR Update; (when i execute this statement and i guess that this will lock the
7
8222
by: pj | last post by:
Why does M$ Query Analyzer display all numbers as positive, no matter whether they are truly positive or negative ? I am having to cast each column to varchar to find out if there are any negative numbers being hidden from me :( I tried checking Tools/Options/Connections/Use Regional Settings both on and off, stopping and restarting M$ Query Analyer in betwixt, but no improvement.
3
2874
by: Hendry Taylor | last post by:
I have a problem where if I issue a select * from against a database it returns no data, but if I select column from it returns the data. Why would the * not be working as a wildcard?
3
1890
by: abighill | last post by:
Problem ------------- I want to return all URL records from 'fett_url' that are not currently indexed in the lookup table 'fett_url_to_data' where 'data_id=2'. i.e. fields=> url_id, url_title, url_link, url_description
0
8773
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, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
8692
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 effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9276
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9145
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 tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8983
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 protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
5935
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 then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4442
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 last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
2
2495
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2084
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.