JOIN clarification

SQL
1

Hi

I’m just working on JOINs tutorial and the exercise I completed had a slightly different answer.

My answer:

USE
	sql_store;

SELECT 
	orders.order_date,
    orders.order_id,
    customers.first_name,
    shippers.name,
    order_statuses.name
FROM
	orders
LEFT JOIN customers
	ON orders.customer_id = customers.customer_id
LEFT JOIN shippers
	ON orders.shipper_id = shippers.shipper_id
LEFT JOIN order_statuses
	ON orders.status = order_statuses.order_status_id
ORDER BY
	orders.order_id ASC;

Mosh’s answer:

USE
	sql_store;

SELECT 
	orders.order_date,
    orders.order_id,
    customers.first_name,
    shippers.name,
    order_statuses.name
FROM
	orders
JOIN customers
	ON orders.customer_id = customers.customer_id
LEFT JOIN shippers
	ON orders.shipper_id = shippers.shipper_id
JOIN order_statuses
	ON orders.status = order_statuses.order_status_id
ORDER BY
	orders.order_id ASC;

I based everything on a left join because of a previous point Mosh made about keeping consistency of joins where possible.

As a best practice avoid using right joins because when your joining multiple tables and you have left and right and inner join, things get really complex. Someone else reading your code will have difficulty visualising how you are joining these tables.

Avoid using right joins and use left joins instead.

Although I don’t know how this scales but my query takes
10 row(s) returned 0.00051 sec / 0.0000050 sec

While Mosh’s takes
10 row(s) returned 0.00055 sec / 0.0000050 sec

The point being here is that it seems to be the same performance and using the rule of thumb of not mixing up inner and outer left or outer right in multiple joins where unnecessary is a better answer.

This wasn’t meant to be nit-picky but it did seem to be a contradiction from earlier in the lesson and also wondered if there was any down-side to my way?

2

I don’t think this is a left vs right sort of thing, but rather an inner vs outer sort of thing. By saying LEFT JOIN you are saying LEFT OUTER JOIN vs JOIN i.e. INNER JOIN. Your outer join query will return order rows even if the order has no customer, whereas Mosh’s won’t.

1 Like
3

Thanks eelsholz

It’s difficult to visualise these joins anyway but I think I get your point. However, it would never be a case in this situation that an order is completed without a customer since you need to be a customer to place an order (orders.customer_id set to Not NULL).

I should probably look up a visual representation of how these joins work so I’ve got a better understanding in my head. I think I’ve always just wiggled my JOINs until they kind of work rather than ever understanding them completely, this time around I want a better grip :slight_smile:

Thanks for your answer