I'm trying to join two tables, but I keep getting weird results, so I decided to do a

SELECT * 
FROM transactions as t
LEFT JOIN customers as c on t.customer_id = c.id

just to see all the columns. When I looked at the output, the transactions table loaded as expected, but the customers one came up as entirely blank entries, even though it's not actually blank. When I switched the order (so did 'from customers' & left joined transactions), the customer's table came up like normal but now the transactions one was entirely blank. I'm honestly at a loss of what may be causing it and would appreciate any advice!

CodePudding user response：

My best guess for what's going wrong is that you've slightly misunderstood how joins work. That inference is based on this statement of yours:

When I looked at the output, the transactions table loaded as expected, but the customers one came up as entirely blank entries, even though it's not actually blank.

A JOIN fuses two tables together. (The fusion is just in the output, of course, not in the database itself!) Thus, a SELECT query with a join in it will always return one united table and not two.

Another factor is that SELECT * is a poor choice. If the two tables have identically named columns, which I suspect may be the case here, the output could become REALLY confusing! Syntax such as SELECT customers.* is your friend in this case.

CodePudding user response：

A left outer join is an inner join, plus all rows from the left side that do not appear in the inner join, supplemented with NULLs on the right side.

So your inner join is empty, because no rows match join condition, and you get the left table supplemented with NULLs on the right side.