CODEWITHSUNDEEP
sql-server
Newbie
Newbie

Reputation: 771

Need help in understanding this SQL query

Can someone please help me in understanding this SQL code? This is what is actually confusing me more - END = G.CODE

SELECT something, something
FROM ABCD A
JOIN GHIJ G ON 
    CASE    
        WHEN A.CODE = 'not available' THEN 'NA_CODE'
        ELSE A.CODE END = G.CODE
                   LEFT JOIN PRODUCT P ON P.ID = A.ID
                   WHERE P.ID IS NULL;
GO

Thanks Isha

Upvotes: 2

Views: 58

Answers (2)

user5683823
user5683823

Reputation:

There is more you need to understand in this query.

You got a good answer for the "CASE ... END = G.CODE" - indeed, the point there is that "END" does not exist by itself, it is the last key word in the CASE expression syntax.

With that out of the way, note that your query will return no rows. This is because at the end you have

...LEFT JOIN PRODUCT P ON P.ID = A.ID
   WHERE P.ID IS NULL

Rows with P.ID "IS NULL" are not returned in the LEFT JOIN - only rows where P.ID = A.ID and rows where A.ID is NULL are returned. (When P.ID is NULL, the equality with A.ID is UNKNOWN, so the row is NOT returned.)

Upvotes: 1

RanchiRhino
RanchiRhino

Reputation: 774

When you look at properly formatted code you will understand it.

CASE WHEN A.CODE = 'not available' THEN 'NA_CODE' ELSE A.CODE END = G.CODE

consider the join clause like 

 B.XYZ = G.CODE

Where B.XYZ is a case statement for manipulating joining column A.CODE for values 'not available' to 'NA_CODE' so that it could match same value in G.CODE. Except the value 'not available' everything is fine so the case statement only manipulates 'not available' to 'NA_CODE', so that join clause could match it with value inside G.CODE.

Look for case statement for more details.

Hope you understood.

Here :-

B.XYZ =[CASE WHEN A.CODE = 'not available' THEN 'NA_CODE' ELSE A.CODE END]

Upvotes: 3

Related Questions