CODEWITHSUNDEEP
sqlmysqldatabasejoincross-join
Shavinda Wanniarachchi
Shavinda Wanniarachchi

Reputation: 11

How to join two tables in MySQL using specific columns?

Screen capture

I want to write a query to get the customer name and product name order by first name.

I have two tables in my MySQL database, let's call them customer details table and product detail table. Each table has several columns, but I only want to join them based on specific columns. For example, let's say I want to join customer details table.first_name with product detail table.product_name.

Could someone please provide me with the correct MySQL query syntax to achieve this? Also, if there are any performance considerations or best practices I should be aware of when joining tables in this manner, I would appreciate any insights.

Upvotes: -1

Views: 213

Answers (1)

Shane Dushyantha
Shane Dushyantha

Reputation: 102

As your image, it has customer_id in both tables which we can use for joining.
Or you can join with any column name with following syntax.

But Joining using "strings" might be caused to following issues

  • Performance Overhead
  • Inflexibility
  • Difficulty in Index Usage
  • Resource Consumption

To avoid these issues, it's generally recommended to join using "int"

SELECT
  `c`.`first_name`,
  `p`.`product_name`
FROM `customer_details` AS `c`
JOIN `product_details` AS `p` ON `c`.`customer_id` = `p`.`customer_id`
ORDER BY
`c`.`first_name`;

MySQL Joins By w3schools

Upvotes: 0

Related Questions