CODEWITHSUNDEEP
mysqlsqljoinmariadbwhere-clause
MatthewChang
MatthewChang

Reputation: 91

How to avoid writing lengthy where clauses in SQL when you join 2 tables with identical schema and check for equality on all fields but one?

Without writing a lot of conditions joined by AND in the where clause, can you inner join 2 tables with identical schema in SQL such that the where clause checks for equality on every field between the two tables for all but one field? The SQL statement gets too long if there are a lot of fields (say, 200 fields). I'm using MariaDB.

For instance, say I have the following where 'id' is the primary key:

TABLE_A(id, name, date, origin, residence, gender, age, height, weight)

TABLE_B(id, name, date, origin, residence, gender, age, height, weight)

When I inner join TABLE_A and TABLE_B, to get all the rows for which the value in TABLE_A and the corresponding value in TABLE_B are the same for all fields but id, I can only think of doing the following:

SELECT * FROM TABLE_A INNER JOIN TABLE_B WHERE 
    TABLE_A.name = TABLE_B.name AND 
    TABLE_A.date = TABLE_B.date AND 
    TABLE_A.origin = TABLE_B.origin AND 
    TABLE_A.residence = TABLE_B.residence AND 
    TABLE_A.gender = TABLE_B.gender AND 
    TABLE_A.age = TABLE_B.age AND 
    TABLE_A.height = TABLE_B.height AND 
    TABLE_A.weight = TABLE_B.weight;

Is there a better way that does not require writing a super long WHERE clause?

Upvotes: 1

Views: 166

Answers (2)

Rick James
Rick James

Reputation: 142453

If you tell us what the real goal is, we might be able to come up with a solution that avoids that messy WHERE. Meanwhile, here are some related queries:

This will dedup the set of rows:

        ( SELECT * FROM TableA )
        UNION DISTINCT
        ( SELECT * FROM TableB )

So will this:

SELECT DISTINCT *
    FROM
    (
        ( SELECT * FROM TableA )
        UNION ALL
        ( SELECT * FROM TableB )
    )

Here's another approach to think about. It's still a lot of typing, but you can copy and paste to do some of the work:

SELECT ...
    FROM TableA
    JOIN TableB
      ON CONCAT_WS("\t", a.name, a.date, ... ) =
         CONCAT_WS("\t", b.name, b.date, ... )

How to get the list of columns without typing them:

SET group_concat_len = 12345;  -- something big enough = 12345;
SELECT  GROUP_CONCAT(column_name)
    FROM  information_schema.columns
    WHERE  table_schema = '...'    -- use the database name
      AND  table_name = 'Table_A'
      AND  column_name != 'id';    -- to exclude `id`

Upvotes: 0

philipxy
philipxy

Reputation: 15158

Make a subquery from each table by renaming the one column to different names via select as. Then natural join the subqueries. This does an inner join using all common/remaining columns. (Or just use one subquery & the other original table.)

(Note though that if your specification is to join in the future on exactly the columns that you are joining on now & more columns might get added to one or both tables then you need to list the current columns--say, in a using. This is similar to select * vs select of specific columns. Ideed, after you join how do you expect to get at all the current columns or current join columns? If you use a build process to reduce redundancy for selects then you could just use it on an inner join.)

Upvotes: 2

Related Questions