1

I have the following statement:

SELECT tableA.*, tableB.* FROM tableA, tableB WHERE tableA.userUUID = ? AND tableB.uuid=tableA.tableBUUID

So when a record in table A has a valid tableBUUID and the tableBUUID exists in tableB then the query runs file. But when I removed the tableBUUID from table B without removing it from the record in tableA then the query returns nothing (Which is obviously expected)

I am wondering how I can change the query so that even if the tableBUUID does not match anything, the query will still return what it can even if its nothing/ blank columns from tableB?

Thanks all

Talktomegoose
  • 83
  • 6
  • You need to use a `JOIN`, specifically a `LEFT JOIN`. Your current syntax is akin to using an implicit `INNER JOIN`. Rather than `FROM tableA, tableB`, you'll want `FROM tableA LEFT JOIN tableB ON `, where that matching condition is what "matches up" the rows in table A to table B (`tableB.uuid = tableA.tableBUUID`). Side note, SQL statements don't have to be one line, take advantage of newlines for readability. – Rogue Sep 21 '22 at 18:56

1 Answers1

1

You can achieve this via a LEFT JOIN:

SELECT tableA.*, tableB.* 
  FROM tableA 
  LEFT JOIN tableB ON tableB.uuid = tableA.tableBUUID
WHERE tableA.userUUID = ?

Fiddle.

Performing a LEFT JOIN will return data from tableA and matching records from tableB if it exists in tableB, otherwise it'll return only the records from tableA.

LEFT JOIN

In the syntax you posted above in your question, you're essentially doing a INNER JOIN by comma separating your tables. This is the old syntax, moving forward you should explicitly write INNER JOIN like so:

SELECT * FROM tableA a INNER JOIN tableB b ON a.column_key = b.column_key

Read more about it in the following previous question:
What's the difference between comma separated joins and join on syntax in MySQL?

griv
  • 2,098
  • 2
  • 12
  • 15