0

I am following a tutorial for learning SQL and working on Google BigQuery Sandbox. For FULL OUTER JOIN my result is showing in different order than the result in the video. How can I fix this? (P.S. I am using the dataset given by them and I created the dataset and tables similar, and we both are using the exact same code.)

`SELECT employees.name AS employee_name,
   employees.role AS employee_role,
   departments.name AS department_name
FROM employee_data.employees
FULL OUTER JOIN employee_data.departments
ON employees.department_id = departments.department_id`

Result I am getting (Julie Jones in row 1), [enter image description here](https://i.stack.imgur.com/HF4o5.png)

But the result provided in the tutorial (expected result) (Dave Smith in row 1), enter image description here

GMB
  • 216,147
  • 25
  • 84
  • 135
noob_maXPro_scientist
  • 3
  • 3
  • 3
    In general in database theory order does not matter and is not guaranteed ... you can control order (generally, but there are pitfalls) using `ORDER BY` clauses in your queries. If you get the same results, but in a different order, that is fine. – topsail Feb 05 '23 at 16:14

1 Answers1

0

This is technically a duplicate of Why do results from a SQL query not come back in the order I expect?.

You should use the ORDER BY clause to enforce the order of your columns. Since you are using FULL OUTER JOIN you should probably expect a handful of NULL values.

You can handle those in your Order By and put them first by adding ISNULL(MyNullColumnName), MyNullColumnName to your order by clause.

I've thrown together a very small simple fiddle to demonstrate managing nulls and how they are displayed in your query you may find useful. MySql doesn't include a NULLS FIRST/LAST like some other RDBMS to my knowledge so it must all be done through the order by.

Ordering with NULL in MySql

ClearlyClueless
  • 545
  • 2
  • 13