CODEWITHSUNDEEP
sql-server
Bob Smith
Bob Smith

Reputation: 841

SQL Server - Combining OUTER and INNER joins

I have 3 tables and I have to inner join Table A with Table B but a left outer between Table A and Table C.

Can I combine outer and inner join in the same query? I can nest queries and attain the desired result but I am unable to do both the joins in the same query. It appears that in other SQL languages, the order of joining is important. Is this the case in SQL Server as well?

Ok, here's the scenario.

Consider 3 tables. Table A, Table F, Table D.

I will need the recordset to contain all rows in D irrespective of whether it exists in F (after it's inner joined with A). So, a outer join comes to mind. What I would need is:

  1. First do a inner join between A and F to get a set (this may be a null set)
  2. Then do a outer join with the recordset in (1) with D

Upvotes: 18

Views: 64247

Answers (9)

paparazzo
paparazzo

Reputation: 45096

This comes up on 1 to zero or many where the many has a FK.

Two approaches
order of the on
put the outer join last

Unfortunate you changed table names. I will use the later names. 

declare @TableD TABLE (PeopleID int primary key, Name varchar(10));
INSERT INTO @TableD VALUES
       (1, 'Chris')
     , (2, 'Cliff')
     , (3, 'Heather');

declare @TableA TABLE (ThingID int primary key, ThingName varchar(10))
INSERT INTO @TableA VALUES
       (14, 'Bike')
     , (17, 'Trailer')
     , (18, 'Boat');

declare @TableF TABLE (PeopleID int, ThingID int, primary key (PeopleID, ThingID));
INSERT INTO @TableF VALUES
       (1, 18)
     , (1, 17)
     , (2, 14);

SELECT D.Name, A.ThingName
FROM @TableD D 
LEFT JOIN @TableF F
     JOIN @TableA A 
       ON A.ThingID = F.ThingID 
  ON F.PeopleID = D.PeopleID
order by D.Name, A.ThingName;

SELECT D.Name, A.ThingName
FROM @TableF F 
JOIN @TableA A 
  ON A.ThingID = F.ThingID 
right join @TableD D
  ON D.PeopleID = F.PeopleID 
order by D.Name, A.ThingName;

Name       ThingName
---------- ----------
Chris      Boat
Chris      Trailer
Cliff      Bike
Heather    NULL

Upvotes: 0

gamov
gamov

Reputation: 3859

For my case, I needed to put table aliases for my query to work properly:

SELECT * FROM ("purchased_items" p1
     INNER JOIN "purchase_orders" po1 ON (po1."id" = p1."purchase_order_id")) AS p4
LEFT OUTER JOIN (purchased_items p2
      INNER JOIN "purchase_orders" po2 ON (po2."id" = p2."purchase_order_id")) AS p5
ON (p4.item_variant_id = p5.item_variant_id AND p4.delivery_date < p5.delivery_date) 
WHERE p5.delivery_date IS NULL AND p4.delivered <> 0

Upvotes: 11

HLGEM
HLGEM

Reputation: 96552

The problem may not specifically be the join (Anthony showed you how to do wht you described to us).Remember that people often have problems using left joins becasue they try to put something in the where clause referncing the table on the right side of the join thus converting it from an outer join to an inner join (unless you are looknig for those records where the second table field is null which gives you the records in the first table and not the secodn one).

We could help you better if we say the actual code you were using that wasn;t producting the desired results as well as some sample data and sample results.

Upvotes: 0

Powerlord
Powerlord

Reputation: 88796

The order shouldn't matter.

Venn Diagram

Here's a Venn Diagram from Wikimedia Commons. Regardless of the query order, you're going to get the overlap between circles A and B, with nulls for C's columns where C isn't overlapping the combination of A and B.

Upvotes: 2

Jay S
Jay S

Reputation: 7994

From your follow-up, it sounds like you want a 'conditional' inner join.

Essentially, an "If A and B have a record, INNER JOIN to C".

However, you are likely running into the problem where the INNER JOIN in your query is not showing records where A has no records associated to B or C. If they are at the same 'scope', the INNERS will always run, you can't conditionally have them run based on their order.

You either have to use two LEFT joins and filter out the records you don't want, or alternatively use a View to scope the INNER JOIN.

Ex. A LEFT JOIN vw_MyView ON A.ID=vw_MyView.A_ID

Where MyView has tables B and C with your INNER JOIN. This will allow the INNER JOIN to be run inside of the view, and then you can LEFT JOIN to the results.

Upvotes: 1

kristof
kristof

Reputation: 53824

If I understand correctly you want something like this:

select
    *
from
    a 
    left outer join c
        inner join b on c.bID = b.ID
    on a.cID = c.ID

Upvotes: 21

Eppz
Eppz

Reputation: 3206

Yes you can do both is the same query, and yes the order is important.

Upvotes: 0

AnthonyWJones
AnthonyWJones

Reputation: 189447

Sure you can do the join in the same query:-

FROM TableA a
INNER JOIN Table b ON a.TableA_ID = b.TableA_ID
LEFT OUTER JOIN Table c ON a.TableA_ID = c.TableA_ID

Upvotes: 4

gkrogers
gkrogers

Reputation: 8356

You can use both inner and outer joins in the same query, but their order is important. See this question:
Inner join & outer join; is the order of tables in from important?

Upvotes: 2

Related Questions