Query that displays the data if they have the same data from table 1 to table 2-CodePudding

My target is to display the first name and last name with the same data on my target output. I have provided a table below for your reference.

Table 1: tbl_user

first_name	last_name
roger	paul
law	marshall
steve	fox
nina	williams
sakura	flower

Table 2: tbl_account

first_name	last_name
roger	paul
law	marshall
steve	fox
nina	kicks
sakura	hana

My target output:

first_name	last_name
roger	paul
law	marshall
steve	fox

As we can see on the target output, there is no "Nina williams" and "sakura flower". It happens because, on the first table and the second table, their first name and last name didn't match.

Any idea on how I can achieve my target? Thank you very much.

CodePudding user response：

A simple inner join should work here:

SELECT u.first_name, u.last_name
FROM tbl_user u
INNER JOIN tbl_account a
    ON a.first_name = u.first_name AND
       a.last_name = u.last_name;

Another way, using a union:

SELECT first_name, last_name
FROM
(
    SELECT first_name, last_name FROM tbl_user
    UNION ALL
    SELECT first_name, last_name FROM tbl_account
) t
GROUP BY first_name, last_name
HAVING COUNT(*) > 1;