Checking if in a table in mysql are two rows with the same elements-CodePudding

Let's say we have the tables: table0 and table1. In both table0 and table1 we store a name, age, and date.

How can I check if an entry from table0 and an entry from table1 have the same name and age?

CodePudding user response：

Simple take join with where clause t1.name = t2.name and t1.age = t2.age.

CodePudding user response：

You should be able to "MINUS" or "EXCEPT" depending on the flavor of SQL used by your DBMS.

select * from table0
minus
select * from table1

If the query returns no rows then the data is exactly the same.

CodePudding user response：

Using relational operators:

SELECT * FROM Table0
UNION 
SELECT * FROM Table1
EXCEPT 
SELECT * FROM Table0
INTERSECT
SELECT * FROM Table1;

Change EXCEPT to MINUS for Oracle.

Slightly picky point: the above relies on operator precedence, which according to the SQL Standard is implementation dependent, so YMMV. It works for SQL Server, for which the precedence is:

Expressions in parentheses
INTERSECT
EXCEPT and UNION evaluated from left to right.