CODEWITHSUNDEEP
sqlinner-join
Neil Keicher
Neil Keicher

Reputation: 39

Difficulty Understanding Logic of Joines

I'll use the following query to illustrate my question:

select a.shipperid,
       b.orderid,
       b.custid
from shippers a inner join orders b

on a.shipperid = b.shipperid

Shipperid is the primary key in the shippers table, and it is also the foreign key in the orders table.
There are only three shipping IDs, and each of them is associated with many different orders in the order table. The join will match the tables when the shipper IDs match.

However, each of the shipper IDs in the shippers table is associated with many, many different rows in the orders table.

So, how do I know that the rows in the shippers table aren't being match with the rows in the orders table in an arbitrary manner?

Upvotes: 0

Views: 453

Answers (2)

Andrew Luo
Andrew Luo

Reputation: 927

your query only asks to get all the (shipper id, orderid, custid) tuples that could possibly be related to each other. In other words, for each tuple returned, then shipperid has at some point shipped orderid to custid. Now it is up to you if you want to to further constrain the query to narrow down to a subset of the results on other criteria.

Upvotes: 0

Rahul
Rahul

Reputation: 2307

In your current query you are doing inner join which will give you output of only matching lines::

Joins

See in the image below for your better understanding.

So to answer your question, you need to do Left join in your case like::

select a.shipperid,
       b.orderid,
       b.custid
from shippers a 
left join orders b

on a.shipperid = b.shipperid

the result of this will be with null value of Orders if there is not any order on that shipping.

Upvotes: 1

Related Questions