How to return data from two tables only when the column value in one table is same as another table using MySQL?

Question

I'm new to MySQL and I want to return data from two tables only when the Mobile numbers in both the table are same. I don't want any null values. How can I achieve this?

Example:

Table A 
UserID  CandidateName  CurrentMobile  CurrentDistrict  Email      Centre 
1       Max            98234          Chennai          aaa@a.com  A
2       Raju           97364          Salem            bbb@b.com  B
3       Ramesh         99873          Trichy           ccc@c.com  C 

Table B
Name     MobileNumber  District
Maximus  98234         Salem 
Ramesh   99873         Trichy
Venkat   98376         Chennai 

I want the following result:

UserID  CandidateName  Name     CurrentDistrict  District  Email      Centre  MobileNumber 
1       Max            Maximus  Chennai          Salem     aaa@a.com  A       98234
3       Ramesh         Ramesh   Trichy           Trichy    ccc@c.com  C       99873 

I tried to use UNION, but it gives null values in some columns and also returns all the data from both the tables.

Answer 1

You want to join the two tables, on the condition that A.CurrentMobile is the same as B.MobileNumber:

SELECT
  A.UserID,
  A.CandidateName,
  B.Name,
  A.CurrentDistrict,
  B.District,
  A.Email,
  A.Centre,
  B.MobileNumber
FROM
  A,
  B
WHERE
  A.CurrentMobile = B.MobileNumber

Alternatively you can use this syntax for the same result:

SELECT
  ... (all the same fields again)
FROM
  A
INNER JOIN B ON A.CurrentMobile = B.MobileNumber

The query will be much faster if you have an index on the two fields you use for the join (that is, the mobile number fields). Read more about joins here and here.