CODEWITHSUNDEEP
mysqlsqljoin
Monika Upwanshi
Monika Upwanshi

Reputation: 3

Concatenating composite keys

I have to join 2 tables (employee and student) using inner join and fetch the matching records. I have 3 primary keys name, age, address. I am using the following query.

SELECT 
    a.name,b.name,a.age,b.age,a.salary,b.salary,a.address,b.address 
FROM
    employee a 
INNER JOIN 
    student b ON a.name = b.name AND a.age = b.age AND a.address = b.address 
WHERE 
    (a.name = b.name OR a.age = b.age OR a.address = b.address)

Is there any way I can concat the primary keys without using AND in the ON condition ?

How can the count of matching value for each column be fetched?

Can I write the query using CASE WHEN statement?

Upvotes: 0

Views: 287

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562711

Join syntax has an option for a USING (...) clause as an alternative to ON .... See example:

select a.name, b.name, a.age, b.age, a.salary, b.salary, a.address, b.address 
from employee a inner join student b 
  using (name, age, address)

The meaning of USING() is that it assumes (1) that the columns exist with the same name in both tables, and (2) that you want to compare them with =, and (3) all columns have to be equal to the column of the same name in the other table, as if you did AND between the terms (just like you're doing in your query).

This USING syntax is of course the same as if you had done the longer syntax with ON ... AND ... AND ... like you did. Logically, they're the same. But USING allows you to write the query a little more briefly.

The other downside of USING is that it doesn't use qualified columns like table.column. So if you had done a join with a third table, and that table also had some columns with names in common, it would become inclear which tables you want to join.

Upvotes: 2

Related Questions