0

I am learning MySQL and was taught that the USING clause is a much simpler method of comparing two columns from different tables than the ON clause. After learning this I decided to download the example database from mysqltutorial.com and began experimenting. Not long after, I noticed inconsistencies. The number of outputted rows is vastly different. The query using the ON clause outputs 122 rows while the query with the USING clause outputs 326 total rows. Below are the two queries that outputted different results.

SELECT *
FROM customers
INNER JOIN orders USING(customerNumber)

SELECT *
FROM customers
INNER JOIN orders ON customers.customerNumber = orders.customerNumber

Is this issue user-error or is USING and ON much different that I originally thought?

Ergest Basha
  • 7,870
  • 4
  • 8
  • 28
ecracs
  • 1
  • 2
  • 1
    please update your question to show (as text, not images) output of `show create table customers;` and `show create table orders;` – ysth Jul 07 '22 at 18:14
  • 1
    @SalmanA I'd thought using was nonstandard too, but others here have asserted otherwise – ysth Jul 07 '22 at 18:20
  • 1
    @raina77ow nothing I see in that question would explain this difference in rows – ysth Jul 07 '22 at 18:21
  • 3
    Note as well an answer in the linked question which explicitly states that [phpMyAdmin returns erroneous results from USING](https://stackoverflow.com/a/50750211/1786065). – Rogue Jul 07 '22 at 18:25
  • @ysth yes looks like it _is_ part of sql92. But I've used sql for 18 years without using `using` – Salman A Jul 07 '22 at 18:26
  • I would definitely regard 'USING' as non standard, have used sql for a similar length of time to @SalmanA and also never having heard of it! – deep64blue Jul 07 '22 at 19:08
  • You can use `USING` when both the tables share a column of the exact same name on which they join. If you run both queries from terminal you would get the same result. Personally I never use phpmyadmin. https://dev.mysql.com/doc/refman/8.0/en/join.html – Ergest Basha Jul 07 '22 at 19:08
  • "mysqltutorial.com" is just an advertiser showing a list of links; can you provide the actual source for your example database? – ysth Jul 07 '22 at 19:16
  • 1
    `USING` is definitely standard SQL, and I use it pretty regularly. The columns must exist by the same name in both tables, and the join expression you intend to use must be equality (`=`). – Bill Karwin Jul 07 '22 at 19:38
  • The two join forms do behave the same. I would assume you are making a mistake in your observation. Either querying different data, or else the data has changed between your tests. You need to show a reproducible case (hopefully a dbfiddle) that demonstrates a difference. – Bill Karwin Jul 07 '22 at 19:39
  • I used the mysql command line interface with my MAMP installation and the result set count from the INNER JOIN ON and the INNER JOIN USING was the same at 326 rows. Looks like PHPmyAdmin is jacked up with its INNER JOIN implementation. – ecracs Jul 07 '22 at 20:56
  • @deep64blue `USING` is standard, it is defined in ISO/IEC 9075-2:2016 ("SQL:2016"), in section 7.10 , and it has been in the standard at least since SQL-92. – Mark Rotteveel Jul 09 '22 at 08:37

0 Answers0