CODEWITHSUNDEEP
sqlsql-serverleft-joinright-join
Rashid Bagheri
Rashid Bagheri

Reputation: 95

Sql query: How to use right outer join correctly

I have to tables: Register and Price.

CREATE TABLE [dbo].[Register](
[RegisterID] [int] NULL,
[GroupID] [int] NULL,
[TestID] [int] NULL)

CREATE TABLE [dbo].[Price](
[ID] [int] NULL,
[GroupID] [int] NULL,
[Price] [bigint] NULL,
[Status] [bit] NULL)

Data in tables

Assuming information similar to the image above, consider the following query

SELECT        Price.*
FROM            Register RIGHT OUTER JOIN Price ON Register.GroupID = Price.GroupID
WHERE        (Price.Status = 1) AND (Register.TestID = 50)

The output will be displayed as shown below.

enter image description here

My expectation is that the first and second rows of the price table will be displayed. So where is my mistake and how should I change the query to get the right output?

Upvotes: 0

Views: 266

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522752

The restriction on the Register table which currently appears in the WHERE clause would have to be moved to the ON clause to get the behavior you want:

SELECT p.*
FROM Register r
RIGHT JOIN Price p ON r.GroupID = p.GroupID AND r.TestID = 50;
WHERE p.Status = 1

Note that more typically you would express the above via a left join:

SELECT p.*
FROM Price p
LEFT JOIN Register r ON r.GroupID = p.GroupID AND r.TestID = 50
WHERE p.Status = 1;

Upvotes: 2

Related Questions