Mysql left join not returning all rows from left table

I have the following tables:

Table 1 "revenue_centers"

1

Table 2 "orders"

3

I use the following query because I want to show all of the revenue centers (even if there were no orders).

4

However, the results only show the revenue centers for which there were orders.

3

I expect to return all of the revenue centers from the left table including those with no records from the right table.

Diagnostic Info

{
"browser-info": {
"language": "en-US",
"platform": "Win32",
"userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/105.0.0.0 Safari/537.36",
"vendor": "Google Inc."
},
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "11.0.16.1+1",
"java.vendor": "Eclipse Adoptium",
"java.vendor.url": "https://adoptium.net/",
"java.version": "11.0.16.1",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "11.0.16.1+1",
"os.name": "Linux",
"os.version": "4.15.0-172-generic",
"user.language": "en",
"user.timezone": "Asia/Shanghai"
},
"metabase-info": {
"databases": [
"h2",
"mysql"
],
"hosting-env": "unknown",
"application-database": "mysql",
"application-database-details": {
"database": {
"name": "MySQL",
"version": "5.7.26-log"
},
"jdbc-driver": {
"name": "MariaDB Connector/J",
"version": "2.7.6"
}
},
"run-mode": "prod",
"version": {
"date": "2022-09-02",
"tag": "v0.44.3",
"branch": "release-x.44.x",
"hash": "7d50282"
},
"settings": {
"report-timezone": null
}
}
}

Hi @mere_data_analyst
Try running that query directly on MySQL. I would guess you would get exactly the same result.
Metabase is just sending your query to the database and showing the results.

Something odd is going on. I've mocked up something using the sample film database in mySQL.
Worked as you'd expect:

What happens when you run that query in a different query tool?
Are you using some variant of MySQL?