LEFT JOIN does not return NULL values

Question

LEFT JOIN does not return NULL values

Navigation

#1 by (1 votes)
#2 by (0 votes)
#3 by (0 votes)
#4 by (0 votes)

3

I have two tables:

AppWindowsEvent:

CREATE TABLE [AppWindowsEvent]
(
[idAppWindowEvent]      INT IDENTITY(1,1)
, [idAppWindow]         INT
, [idMill]              INT
, [idEventType]         INT
, [Order]               INT
, CONSTRAINT PK_idAppWindowEvent PRIMARY KEY ([idAppWindowEvent])
, CONSTRAINT FK_idAppWindowEvent_AppWindow FOREIGN KEY ([idAppWindow]) REFERENCES [AppWindow]([idAppWindow])
, CONSTRAINT FK_idAppWindowEvent_EventType FOREIGN KEY ([idEventType]) REFERENCES [EventType]([idEventType])
)

Event:

CREATE TABLE [Event]       
(
[idEvent] [INT] IDENTITY(1,1) NOT NULL
, [idEventType] [INT] NOT NULL
, [idEntity] [INT] NOT NULL
, CONSTRAINT PK_IdEvent PRIMARY KEY([idEvent])
, CONSTRAINT [FK_Event_EventType] FOREIGN KEY([idEventType]) REFERENCES [EventType] ([idEventType])
)

THE PROBLEM: In the table I have sets of Events for certain filters ( idAppWindow and idMill ), for each combination, the data set can vary, and in Event , I have N number of Event records (of which some can be like no in the AppWindowEvent table), in the Event table it is identified by idEntity.

So, if for a idAppWindow and a idMill , I want to know what events I have with value (and those that have no value) in the Event table, this would be solved easily with a LEFT JOIN , but I do not get the result I want.

In the first case it is as if I made a INNER JOIN , it does not return NULLS values, and in the second case (when the ON of JOIN I add a filter of idEntity works perfect, returns the amount of Events that are configured for idMill and idAppWindow , and the values corresponded in Event , those that do not, is because they are null records).

When I execute this query:

SELECT
*
FROM
AppWindowsEvent AWE
LEFT JOIN Event E ON AWE.idEventType = E.idEventType
WHERE
AWE.idMill = 1
AND AWE.idAppWindow = 1
ORDER BY
AWE.[Order] ASC

The result does not return null values.

But when I execute this other query:

SELECT
*
FROM
AppWindowsEvent AWE
LEFT JOIN Event E ON AWE.idEventType = E.idEventType
AND E.[idEntity] = 1234
WHERE
AWE.idMill = 1
AND AWE.idAppWindow = 1
ORDER BY
AWE.[Order] ASC

The result returns null records.

NOTE: Although the second query is running, it does so for a idEntity specific, in my case I need all idEntity .

sql sql-server

asked by fdarle 28.09.2016 в 16:15

source

4 answers

colspan does not work on the Datatable in C # Upload files and folders in Moodle

score 1 · Answer 1

You are doing a JOIN of two tables for a field that is not the primary key of the second table, which in the end generates an arbitrary result.

Assuming that is not the main problem, you have to evaluate which is the table that contains the most information. That would be the one that should go first in the JOIN.

SELECT
* FROM Event E 
LEFT JOIN AppWindowsEvent AWE 
ON AWE.idEventType = E.idEventType 
WHERE AWE.idMill = 1 AND AWE.idAppWindow = 1 
ORDER BY AWE.[Order] ASC

That way you would get the NULLs you expect.

score 0 · Answer 2

At first I see the sentence correct. Since having entered in the WHERE a condition with a field of the Event table instead LEFT would pass to INNER, which is not the case. Could you try to remove the foreign keys of both tables, see how they behave and tell us?

score 0 · Answer 3

The first query you have written is correct and meets what you need, what is wrong is the second query you do to check the results.

The LEFT JOIN basically shows all the records in the table on the left and their matches with the tables on the right, that is to say that it is enough that there is a record that matches and will no longer show null, I will use the following example to explain it better in your case.

In the tables [AppWindowsEvent] and [Event] I have 2 records in each, as both records match in the idEventType field, the result is 2 records without nulls using the query you published.

Then, when filtering the records of the table [Event] by the field [idEntity] we only have 1 record, therefore when making the verification query that you published the engine does not find a match for a record in the table [ AppWindowsEvent] and shows you the fields as nulls.

score 0 · Answer 4

0

The join will only look for exact matches in this case because it is left in the table, Awe , if you want to return everything even if it has nulls, you must use a union

answered by 30.09.2016 в 05:19