473,698 Members | 2,754 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

left join is strange


Hello,
I have 2 tables:

CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL
);

CREATE TABLE products_daily_ compacted_views (
product INTEGER NOT NULL REFERENCES products,
date DATE NOT NULL DEFAULT ('NOW'::TEXT):: DATE,
count INTEGER NOT NULL
);

The table products has 1785 rows, the table products_daily_ compacted_views
has 768 rows with date = current_date;

I want to list all the products and the number of times each product has
been viewed:

SELECT p.id, p.name, COALESCE(v.coun t, 0) AS views
FROM products p LEFT JOIN products_daily_ compacted_views v ON p.id = v.product
WHERE v.date = current_date OR v.date IS NULL ORDER BY views DESC

The problem with this query is that it doesn't return all the products,
instead of 1785 rows, it returns 1077 rows

This modified query seems to be correct, it returns all the products...

SELECT p.id, p.name, COALESCE(v.coun t, 0) AS views
FROM products p LEFT JOIN products_daily_ compacted_views v
ON p.id = v.product AND v.date = current_date
ORDER BY views DESC

Could anybody explain to me why does this happen ?

Thank you.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 12 '05 #1
4 2186
> Andrei Ivanov wrote:

I want to list all the products and the number of times each
product has
been viewed:

SELECT p.id, p.name, COALESCE(v.coun t, 0) AS views
FROM products p LEFT JOIN products_daily_ compacted_views v ON
p.id = v.product
WHERE v.date = current_date OR v.date IS NULL ORDER BY views DESC

The problem with this query is that it doesn't return all the
products,
instead of 1785 rows, it returns 1077 rows And that is exactly as it should be.
You will get the left joined combination of p and v, but the filter in
the where is applied afterwards on all those combinations.

This modified query seems to be correct, it returns all the
products...

SELECT p.id, p.name, COALESCE(v.coun t, 0) AS views
FROM products p LEFT JOIN products_daily_ compacted_views v
ON p.id = v.product AND v.date = current_date
ORDER BY views DESC

Could anybody explain to me why does this happen ?

Here you apply your filter to the elements of v, before joining them to
the elements of p.

Best regards,

Arjen


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #2


On Mon, 8 Dec 2003, Arjen van der Meijden wrote:
Andrei Ivanov wrote:

I want to list all the products and the number of times each
product has
been viewed:

SELECT p.id, p.name, COALESCE(v.coun t, 0) AS views
FROM products p LEFT JOIN products_daily_ compacted_views v ON
p.id = v.product
WHERE v.date = current_date OR v.date IS NULL ORDER BY views DESC

The problem with this query is that it doesn't return all the
products,
instead of 1785 rows, it returns 1077 rows

And that is exactly as it should be.
You will get the left joined combination of p and v, but the filter in
the where is applied afterwards on all those combinations.


I kinda figured that out, but still, being a left join, it should return
all the rows in the table products, which I then filter with
v.date = current_date OR v.date IS NULL.

v.date has 3 possible values: current_date, some other date or NULL, if
there is no corresponding row in products_daily_ compacted_views for that
product.

I filter out only 1 value, and I still should get 1785 rows...


This modified query seems to be correct, it returns all the
products...

SELECT p.id, p.name, COALESCE(v.coun t, 0) AS views
FROM products p LEFT JOIN products_daily_ compacted_views v
ON p.id = v.product AND v.date = current_date
ORDER BY views DESC

Could anybody explain to me why does this happen ?

Here you apply your filter to the elements of v, before joining them to
the elements of p.

Best regards,

Arjen


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 12 '05 #3
> Andrei Ivanov wrote:

On Mon, 8 Dec 2003, Arjen van der Meijden wrote:
Andrei Ivanov wrote:

I want to list all the products and the number of times each
product has
been viewed:

SELECT p.id, p.name, COALESCE(v.coun t, 0) AS views
FROM products p LEFT JOIN products_daily_ compacted_views v ON
p.id = v.product
WHERE v.date = current_date OR v.date IS NULL ORDER BY views DESC

The problem with this query is that it doesn't return all the
products,
instead of 1785 rows, it returns 1077 rows

And that is exactly as it should be.
You will get the left joined combination of p and v, but

the filter in
the where is applied afterwards on all those combinations.


I kinda figured that out, but still, being a left join, it
should return
all the rows in the table products, which I then filter with
v.date = current_date OR v.date IS NULL.

v.date has 3 possible values: current_date, some other date
or NULL, if
there is no corresponding row in
products_daily_ compacted_views for that
product.

I filter out only 1 value, and I still should get 1785 rows...


No, you combine two table using a left join (and yes, you get 1785 rows
from that left join), which then (after the joining) get filtered using
your where.
The values that have the current_date (which are probably none, since
that is taken at the moment of the selection, not at the moment of the
insert) or the NULL will get through, resulting in less than your 1785
rows.

Regards,

Arjen


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 12 '05 #4
Andrei Ivanov <an***********@ ines.ro> writes:
I kinda figured that out, but still, being a left join, it should return
all the rows in the table products, which I then filter with
v.date = current_date OR v.date IS NULL.
v.date has 3 possible values: current_date, some other date or NULL, if
there is no corresponding row in products_daily_ compacted_views for that
product.


Right. Your first query will show products for which (1) there is a v
row with date = current_date, or (2) there is *no* v row at all. If
there is a v row with the wrong date, it will get through the left join
and then be eliminated at WHERE. Because it gets through the left join,
no null-extended row is generated for that product, and so your OR
v.date IS NULL doesn't help.

In your second query, the date condition is considered part of the LEFT
JOIN condition, meaning that if no v rows pass the date condition, a
null-extended row will be emitted.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 12 '05 #5

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

Similar topics

0
2446
by: Marek Lewczuk | last post by:
Hello, I have a strange problem, maybe some of you will be able to explain me something. I use LEFT JOIN as a substitute for subselects. It's true that many subselects can be rewriten using LEFT JOIN. I have made a query which use LEFT JOIN statement and... when there are many LEFT JOIN's (over 3) on the same table MySQL execute this query very long time... few hours or more. Maybe there is something wrong with my table structures... Are...
0
13005
by: Petre Agenbag | last post by:
Hi List Me again. I'm trying to return from multiple tables, the records that have field "information_sent" between two dates. The tables are all related by means of the id of the entry in the main table, ie.. main id entity_name ...
0
2358
by: Soefara | last post by:
Dear Sirs, I am experiencing strange results when trying to optimize a LEFT JOIN on 3 tables using MySQL. Given 3 tables A, B, C such as the following: create table A ( uniqueId int not null default 0 auto_increment, a1 varchar(64) not null default '',
1
3450
by: Paul Bramscher | last post by:
Here's one for pathological SQL programmers. I've got a table of things called elements. They're components, sort of like amino acids, which come together to form complex web pages -- as nodes in trees which form parent-child relationships, sort of like newsgroups. For example, the parent_id field points to another element. Indent_level is there for denormalization purposes, to avoid costly recursive issues in querying. The...
4
4104
by: jbm05 | last post by:
Hi, I'm curious about the computational complexity of a query I have. The query contains multiple nested self left joins, starting with a simple select, then doing a self left join with the results, then doing a self left join with those results, etc. What puzzles me is that the time required for the query seems to grow exponentially as I add additional left joins, which I didn't expect. I expected the inner select to return about 25...
3
10051
by: Dam | last post by:
Using SqlServer : Query 1 : SELECT def.lID as IdDefinition, TDC_AUneValeur.VALEURDERETOUR as ValeurDeRetour FROM serveur.Data_tblDEFINITIONTABLEDECODES def, serveur.Data_tblTABLEDECODEAUNEVALEUR TDC_AUneValeur where def.TYPEDETABLEDECODES = 4
3
23096
by: Ian Boyd | last post by:
i know nothing about DB2, but i'm sure this must be possible. i'm trying to get a client to create a view (which it turns out is called a "Logical" in DB2). The query needs a LEFT OUTER JOIN, but he doesn't know how to do that, or even if he can, and i don't have to time to learn DB2 from scratch right now. The following SQL Query is a trimmed sample of the full View (i.e. Logical) definition - and i would create it on an SQL based...
2
3142
by: tricard | last post by:
Good day all, I have a large outer joined query that I want to have some criteria. The select query is gathering all part numbers from tblPartNumbers, left joining to tblPartNumberVendor (since more than one vendor can make the part), then left joining to tblPartNumberSupplier (since more than one supplier can distribute the vendor's part), then left joining to tblPartNumberCost (since more than one cost can be associated with a single...
1
4033
by: naveenchhibber | last post by:
Hi all pls tell me that the following statment is valid in oracle 9i or 10g.. update ws set received_by_facility = coalesce(rbf_ouk.organizational_unit_id, 0), assigned_to_facility = coalesce(atf_ouk.organizational_unit_id, 0), received_by_team = coalesce(rbt_ouk.organizational_unit_id, 0), assigned_to_team = coalesce(att_ouk.organizational_unit_id, 0)
0
8683
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
8609
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
9170
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...
1
8901
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 Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8871
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
5862
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
4371
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...
0
4622
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3052
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

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.