Cartesian product in query

[CFu]

I'm using this statement in Oracle SQL Developer:

SELECT c.customerid
FROM CUSTOMERS c
INNER JOIN ORDERS o
ON c.customerid=o.customerid

.. and I get a Cartesian product (all rows from ORDERS table instead of just the matching id's). Why is this?

I mean, there are only 10 id's in CUSTOMERS and it returns me 800 rows because there are 800 orders.. I don't understand.

Thank you

 

[root]

it's because you are joining the tables (so c.customerid appears multiple times) and have no limiting function.

what you're doing is joining two tables

basically in table 1 you have say customerid and customer name
in table 2 you have customerid and order id

this is a relational database and enables multiple customers to place multiple orders.

so you have 10 customers, (10 customer id's in the customers table).

they've placed 50 orders.

when you select with your query you're joining the

table 1 looks like
1 bob
2 dave
3 steve

table two looks like
1 bread
1 beans
2 bread
1 sausages

your query is returning the joined tables so the customer id for dave is getting returned as many times as he's ordered stuff.

change the query to select * from customers c inner join orders o on c.customerid=o.customerid and you'll see what I mean about the combined table

you can use select unique if you want to join in this way. (to do what I think you're trying to do)

I guess you're trying to return the customer Id's of people who have ordered.
i.e you can't just say select from customers because steve would be returned even though he's ordered nothing

 

[CFu]

I was trying to return all the customers that have placed orders, but not repeated.
I have achieved this by the following query:

SELECT c.contactname, count(o.customerid) "N. of Orders"
FROM CUSTOMERS c, ORDERS o
WHERE c.customerid=o.customerid GROUP BY c.contactname

There I can see the number of orders each customer has placed (I have no null columns in my tables).

What I wanted in the beginning was this:

SELECT distinct c.contactname, o.customerid
FROM customers c, orders o
WHERE c.customerid=o.customerid
order by 1

Which now works.

 

[root]

distinct and unique is the same keyword in oracle, so you've pretty much came to the same solution I did!