Is there easier syntax for joining MySQL tables based on all columns?

Question

I have two tables with many columns (~50). Is there a way to join these tables using each column without explicitly naming each one in each table?

SELECT * from A
JOIN B
WHERE A.column1=B.column1
AND   A.column2=B.column2
AND   A.column3=B.column3
...
...
...
AND   A.column50=B.column50

There are many questions about various joins on stack (e.g. this one on multiple column joins), but I was unable to find one that addressed this question.

This approach gave me the idea for the following (which yields a syntax error in MySQL).

SELECT  tb1.*, tb2.x
FROM    tableA tb1
        INNER JOIN tableB tb2
            ON tb1.* = tb2.*

Is there something similarly brief to the above syntax that can be used to define this join without writing each column name twice?

Why am I trying to doing this? In case this seems incredibly inefficient, the basis for this join is that there are rows in the first table that I'm planning on deleting provided I can get an exact match to them based on all the columns in the second.

The column names are the same in each table.

This won't work if columns are nullable. Keep in mind that `NULL = NULL` will return `NULL` and you don't get this row in resultset — Lukasz Szozda, Nov 06 '15 at 21:06

score 4 · Answer 1 · answered Nov 06 '15 at 21:17

4

You can try USING when joining the tables. Something like:

SELECT * FROM
    A JOIN B
USING (column1,column2,column3, ... ,column50)

Note that the columns must have the same name in both tables.

answered Nov 06 '15 at 21:17

Soon Ho

61
4

score 2 · Accepted Answer · answered Nov 06 '15 at 21:48

2

Since the column names are the same in both tables you can use a Natural Join.

SELECT a.* FROM a NATURAL JOIN b;

Here is an example using SQL Fiddle

answered Nov 06 '15 at 21:48

SoftwareCarpenter

3,835
3
25
37

Kostas Mitsarakis · Answer 3 · 2015-11-06T21:57:52.837

If there are not nullable columns then you can use this. Note that on phpMyAdmin shows an error but in MySQL console runs correct (it's a phpMyAdmin bug).

SET @sql = NULL;

SELECT GROUP_CONCAT(COLUMN_NAME) INTO @sql
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'table_1' AND table_schema = 'your_database_table_schema ';

SET @sql = CONCAT('SELECT table_1.* FROM table_1 JOIN table_2  USING (', @sql, ')');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

score 0 · Answer 4 · answered Nov 06 '15 at 21:11

As I've attempted this myself, I'll go out on a limb and say "no", there's no syntax allowing you to simplify this comparison.

However, and assuming you don't have NULLable columns (per lad2025), there is at least one workaround:

Because you're matching a large number of columns, you could merge both tables into a temporary table that has at least those columns in common. If you create a primary key that includes all the columns for which you want to consider the rows identical, then your temporary table can only contain one of the rows.

So if you INSERT table A into the temporary table, then INSERT IGNORE table B into the temporary table, then the records of table B that DON'T appear in the temporary table are your matches.

Is there easier syntax for joining MySQL tables based on all columns?

4 Answers4