Here is my table structure:
create table table_a (emp_name varchar(100), dept_name varchar(100));
insert into table_a values ('amar', 'finance');
insert into table_a values ('akbar', 'human_resource');
insert into table_a values ('amar', 'finance');
insert into table_a values ('anthony', 'information_technology');
create table table_b (emp_name varchar(100), dept_name varchar(100));
insert into table_b values ('amar', 'finance');
insert into table_b values ('akbar', 'human_resource');
mysql> select * from table_a as a left join table_b as b on a.emp_name = b.emp_name and a.dept_name = b.dept_name where b.dept_name is null;
+----------+------------------------+----------+-----------+
| emp_name | dept_name | emp_name | dept_name |
+----------+------------------------+----------+-----------+
| anthony | information_technology | NULL | NULL |
+----------+------------------------+----------+-----------+
1 row in set (0.00 sec)
The left join mentioned above works as expected.
However, what I need is one record for "amar" as well because there are 2 entries for amar in table A and only 1 entry in table B. So, the result should look like this...
+----------+------------------------+----------+-----------+
| emp_name | dept_name | emp_name | dept_name |
+----------+------------------------+----------+-----------+
| anthony | information_technology | NULL | NULL |
| amar | finance | NULL | NULL |
+----------+------------------------+----------+-----------+
2 row in set (0.00 sec)
I need this in an environment where count accuracy is more important than data integrity. The table B record number (2) + query results (2) should match with the table A record numbers (4)
UNION
.