Where Clause With Full Outer Join

Question

I may just be setting up my query completely incorrect but my expected result set that I need returned is Nuestra B & Nosotros B since they fall into the date range respectively. I.E. Nuestra B feeduedate >= '20160301' AND fj.feeduedate <='20160330' and for Nosotros B fe.ViolationDate >= '20170601' AND fe.ViolationDate <= '20170606' they fall into place.

However when I run this query attempting to return the result I get 0 returned in my result set.

What is incorrect with my query? Below is sample data & DDL

    CREATE TABLE [dbo].[fei](
    [violatorsName] [varchar](500) NOT NULL,
    [violationNumber] [varchar](100) NOT NULL,
    [violationDate] [date] NOT NULL
) ON [PRIMARY]


CREATE TABLE [dbo].[FJI](
    [violatorsName] [varchar](500) NOT NULL,
    [VIN] [varchar](100) NOT NULL,
    [vfees1] [float] NOT NULL,
    [vfees2] [float] NOT NULL,
    [vfees3] [float] NOT NULL,
    [vfees4] [float] NOT NULL,
    [vfees5] [float] NOT NULL,
    [vfees6] [int] NULL,
    [feeduedate] [date] NULL,
    [totalvfees] [float] NOT NULL,
    [totalvfeespaid] [float] NOT NULL,
    [vfeesremaining] [float] NOT NULL,
    [vfee7] [float] NOT NULL,
    [vfee8] [float] NOT NULL,
    [vfee9] [float] NOT NULL,
    [vfee10] [float] NOT NULL,
    [vfee11] [float] NOT NULL,
    [vfee12] [float] NULL,
    [vfee13] [float] NULL,
    [vfee14] [float] NULL,
    [vfee15] [float] NULL,
    [vfee16] [float] NULL
) ON [PRIMARY]

    INSERT [dbo].[fei] ([violatorsName], [violationNumber], [violationDate]) VALUES (N'Nostra A', N'3244', CAST(0xE63C0B00 AS Date))
INSERT [dbo].[fei] ([violatorsName], [violationNumber],  [violationDate]) VALUES (N'Nuestra B', N'408', CAST(0xE53C0B00 AS Date))
INSERT [dbo].[FJI] ([violatorsName], [VIN], [vfees1], [vfees2], [vfees3], [vfees4], [vfees5], [vfees6], [feeduedate], [totalvfees], [totalvfeespaid], [vfeesremaining], [vfee7], [vfee8], [vfee9], [vfee10], [vfee11], [vfee12], [vfee13], [vfee14], vfee15, vfee16) VALUES (N'Nosotros A', N'41', 1917.71, 0, 898.3, 10.870000, 0, 1906, CAST(0x353B0B00 AS Date), 9948, 9773, 7867, 1429.5, 9296.5, 401.53999999999996, 9371, 2826.88, 20782, 28875.31, 222059.4, 0, 0)
INSERT [dbo].[FJI] ([violatorsName], [VIN], [vfees1], [vfees2], [vfees3], [vfees4], [vfees5], [vfees6], [feeduedate], [totalvfees], [totalvfeespaid], [vfeesremaining], [vfee7], [vfee8], [vfee9], [vfee10], [vfee11], [vfee12], [vfee13], [vfee14], vfee15, vfee16) VALUES (N'Nosotros B', N'211', 2339.45, 0, 1545.14, 2.2, 69.13, NULL, CAST(0x2D3B0B00 AS Date), 6192, 6192, 6192, 0, 6192, 3404, 2788, 3955.9299999999994, 0, 4856.81, 0, 0, 15421.550000000001)
INSERT [dbo].[FJI] ([violatorsName], [VIN], [vfees1], [vfees2], [vfees3], [vfees4], [vfees5], [vfees6], [feeduedate], [totalvfees], [totalvfeespaid], [vfeesremaining], [vfee7], [vfee8], [vfee9], [vfee10], [vfee11], [vfee12], [vfee13], [vfee14], vfee15, vfee16) VALUES (N'Nosotros B', N'211', 2339.45, 0, 1545.14, 2.2, 69.13, NULL, CAST(0x2D3B0B00 AS Date), 6192, 6192, 6192, 0, 6192, 3404, 2788, 3955.9299999999994, 0, 4856.81, 0, 0, 15421.550000000001)
INSERT [dbo].[FJI] ([violatorsName], [VIN], [vfees1], [vfees2], [vfees3], [vfees4], [vfees5], [vfees6], [feeduedate], [totalvfees], [totalvfeespaid], [vfeesremaining], [vfee7], [vfee8], [vfee9], [vfee10], [vfee11], [vfee12], [vfee13], [vfee14], vfee15, vfee16) VALUES (N'Nosotros B', N'311', 2339.45, 0, 1545.14, 2.2, 69.13, NULL, '20170101', 6192, 6192, 6192, 0, 6192, 3404, 2788, 3955.9299999999994, 0, 4856.81, 0, 0, 15421.550000000001)
INSERT [dbo].[FJI] ([violatorsName], [VIN], [vfees1], [vfees2], [vfees3], [vfees4], [vfees5], [vfees6], [feeduedate], [totalvfees], [totalvfeespaid], [vfeesremaining], [vfee7], [vfee8], [vfee9], [vfee10], [vfee11], [vfee12], [vfee13], [vfee14], vfee15, vfee16) VALUES (N'Nosotros B', N'811', 2339.45, 0, 1545.14, 2.2, 69.13, NULL, '20170103', 6192, 6192, 6192, 0, 6192, 3404, 2788, 3955.9299999999994, 0, 4856.81, 0, 0, 15421.550000000001)
INSERT [dbo].[FJI] ([violatorsName], [VIN], [vfees1], [vfees2], [vfees3], [vfees4], [vfees5], [vfees6], [feeduedate], [totalvfees], [totalvfeespaid], [vfeesremaining], [vfee7], [vfee8], [vfee9], [vfee10], [vfee11], [vfee12], [vfee13], [vfee14], vfee15, vfee16) VALUES (N'Nosotros B', N'11111', 2339.45, 0, 1545.14, 2.2, 69.13, NULL, '20170301', 6192, 6192, 6192, 0, 6192, 3404, 2788, 3955.9299999999994, 0, 4856.81, 0, 0, 15421.550000000001)
INSERT [dbo].[FJI] ([violatorsName], [VIN], [vfees1], [vfees2], [vfees3], [vfees4], [vfees5], [vfees6], [feeduedate], [totalvfees], [totalvfeespaid], [vfeesremaining], [vfee7], [vfee8], [vfee9], [vfee10], [vfee11], [vfee12], [vfee13], [vfee14], vfee15, vfee16) VALUES (N'Nosotros B', N'77711', 2339.45, 0, 1545.14, 2.2, 69.13, NULL, '20170301', 6192, 6192, 6192, 0, 6192, 3404, 2788, 3955.9299999999994, 0, 4856.81, 0, 0, 15421.550000000001)



  SELECT ISNULL(fe.violatorsName,fj.violatorsName)
     , TotalSW  = COUNT(fe.violationNumber)
     , TotalSNT = COUNT(fj.VIN)
     , totalvfees = SUM(totalvfees)
     , calculatedvfee = SUM(COALESCE(totalvfees,0)-COALESCE(vfee9,0))
     , AdminFee = SUM(COALESCE(vfee12,0))
     , SecFee = SUM(COALESCE(vfee13,0))
FROM fei fe
FULL OUTER JOIN fji fj ON fj.violatorsName =fe.violatorsName
WHERE fe.ViolationDate >= '20170601' AND fe.ViolationDate <= '20170606'
AND fj.feeduedate >= '20160301' AND fj.feeduedate <='20160330'
GROUP BY ISNULL(fe.violatorsName, fj.violatorsName)


<strong>EDIT</strong><br>
    SELECT ISNULL(fe.violatorsName,fj.violatorsName)
     , TotalSW  = COUNT(fe.violationNumber)
     , TotalSNT = COUNT(fj.VIN)
     , totalvfees = SUM(totalvfees)
     , calculatedvfee = SUM(COALESCE(totalvfees,0)-COALESCE(vfee9,0))
     , AdminFee = SUM(COALESCE(vfee12,0))
     , SecFee = SUM(COALESCE(vfee13,0))
FROM fei fe
FULL OUTER JOIN fji fj ON fj.violatorsName =fe.violatorsName
WHERE ((fe.ViolationDate >= '20170601' AND fe.ViolationDate <= '20170606')
OR (fj.feeduedate >= '20160301' AND fj.feeduedate <='20160330'))
GROUP BY ISNULL(fe.violatorsName, fj.violatorsName)

Looks like changing it to an OR worked. Testing with larger set of data. — user2676140, Commented Jun 22, 2017 at 16:04

Aaron Bertrand · Accepted Answer · 2017-06-22 16:07:24Z

9

When you have an OUTER JOIN, any WHERE clause against any outer table turns that into an inner table. Meaning only rows where that predicate can be evaluated will make it through. Effectively a WHERE clause in an OUTER JOIN makes that an INNER JOIN.

I would try:

AND ((fe.ViolationDate >= '20170601' AND fe.ViolationDate <= '20170606')
  OR  (fj.feeduedate >= '20160301' AND fj.feeduedate <='20160330'))

Or:

WHERE (fe.ViolationDate >= '20170601' AND fe.ViolationDate <= '20170606')
  OR  (fj.feeduedate >= '20160301' AND fj.feeduedate <='20160330')

answered Jun 22, 2017 at 16:07

Aaron Bertrand

182k28 gold badges402 silver badges619 bronze badges

Do the parenthesis matter?
– user2676140
Commented Jun 22, 2017 at 16:10
2

@user2676140 It can in some cases. I always try to use them for the reader, not necessarily for SQL Server.
– Aaron Bertrand
Commented Jun 22, 2017 at 16:15
Testing this on my production data it seems to be returning a count of all rows in the table regardless of date criteria for TotalSW & TotalSWT- for that specific violatername
– user2676140
Commented Jun 22, 2017 at 16:17
@user2676140 So on data different from that in the question? Can you put enough sample data into the question to reproduce the problem here? (I mean, this sample data of 4 rows doesn't have a single match, if that's similar to production data then maybe you should consider a union all instead of a full outer join. Or including sample data that includes matches. In any case, please clearly indicate your desired output, including all column values, not just which names you expect in the result.)
– Aaron Bertrand
Commented Jun 22, 2017 at 16:21
Take a look at updated DDL. It returns 2 for Nosotros B even tho only 1 date for feeduedate falls between 20160301 and 20160330
– user2676140
Commented Jun 22, 2017 at 16:38

| Show 1 more comment

ypercubeᵀᴹ · Accepted Answer · 2017-06-22 17:44:14Z

Another way to have the same results - but probably with a different execution plan - is by getting the rows wanted from each table separately and then FULL JOIN them:

FROM 
    ( SELECT * FROM fei 
      WHERE ViolationDate >= '20170601'
        AND ViolationDate <= '20170606'
    ) AS fe
  FULL OUTER JOIN 
    ( SELECT * FROM fji 
      WHERE feeduedate >= '20160301' 
        AND feeduedate <= '20160330'
    ) AS fj 
  ON 
    fj.violatorsName = fe.violatorsName
-- no WHERE

Tested at dbfiddle.uk.

SELECT ISNULL(fe.violatorsName,fj.violatorsName)
     , TotalSW  = COUNT(fe.violationNumber)
     , TotalSNT = COUNT(fj.VIN)
     , totalvfees = SUM(totalvfees)
     , calculatedvfee = SUM(COALESCE(totalvfees,0)-COALESCE(vfee9,0))
     , AdminFee = SUM(COALESCE(vfee12,0))
     , SecFee = SUM(COALESCE(vfee13,0))
FROM fei fe
FULL OUTER JOIN fji fj ON fj.violatorsName =fe.violatorsName
WHERE (fe.ViolationDate >= '20170601' AND fe.ViolationDate <= '20170606'
 OR fj.feeduedate >= '20160301' AND fj.feeduedate <='20160330'
) 
GROUP BY ISNULL(fe.violatorsName, fj.violatorsName);
GO

(No column name) | TotalSW | TotalSNT | totalvfees | calculatedvfee | AdminFee |  SecFee
:--------------- | ------: | -------: | ---------: | -------------: | -------: | ------:
Nosotros B       |       0 |        2 |      12384 |           5576 |        0 | 9713.62
Nuestra B        |       1 |        0 |       null |              0 |        0 |       0

Warning: Null value is eliminated by an aggregate or other SET operation.

SELECT ISNULL(fe.violatorsName,fj.violatorsName)
     , TotalSW  = COUNT(fe.violationNumber)
     , TotalSNT = COUNT(fj.VIN)
     , totalvfees = SUM(totalvfees)
     , calculatedvfee = SUM(COALESCE(totalvfees,0)-COALESCE(vfee9,0))
     , AdminFee = SUM(COALESCE(vfee12,0))
     , SecFee = SUM(COALESCE(vfee13,0))
FROM 
    (select * from fei WHERE ViolationDate >= '20170601' AND ViolationDate <= '20170606') fe
FULL OUTER JOIN 
    (select * from fji WHERE feeduedate >= '20160301' AND feeduedate <='20160330') fj 
  ON fj.violatorsName =fe.violatorsName
GROUP BY ISNULL(fe.violatorsName, fj.violatorsName);
GO

(No column name) | TotalSW | TotalSNT | totalvfees | calculatedvfee | AdminFee |  SecFee
:--------------- | ------: | -------: | ---------: | -------------: | -------: | ------:
Nosotros B       |       0 |        2 |      12384 |           5576 |        0 | 9713.62
Nuestra B        |       1 |        0 |       null |              0 |        0 |       0

Warning: Null value is eliminated by an aggregate or other SET operation.

Stack Exchange Network

Where Clause With Full Outer Join

2 Answers 2

Your Answer

Not the answer you're looking for? Browse other questions tagged
sql-server
sql-server-2008-r2
t-sql
or ask your own question.

Hot Network Questions

Where Clause With Full Outer Join

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged sql-serversql-server-2008-r2t-sql or ask your own question.

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
sql-server
sql-server-2008-r2
t-sql
or ask your own question.