CODEWITHSUNDEEP
mysqlsqljoin
1der
1der

Reputation: 205

Duplicate column in SELECT * JOIN ON primary key = foreign key operation?

I always use JOINS to link two tables on the primary key of the main table to the foreign key of the second. In this example database books with 2 tables:

CREATE TABLE IF NOT EXISTS `classics` (
  `isbn` char(13) NOT NULL,
  `author` varchar(128) NOT NULL,
  `title` varchar(128) NOT NULL,
  `category` varchar(16) DEFAULT NULL,
  `year` char(4) NOT NULL,
  PRIMARY KEY (`isbn`),
  FULLTEXT KEY `author` (`author`,`title`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `customers` (
  `name` varchar(128) DEFAULT NULL,
  `isbn` char(13) NOT NULL DEFAULT '',
  PRIMARY KEY (`isbn`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

I often do queries similar to this:

name    isbn    isbn    author  title   category    year
(row 1 values ...)
(row 2 ...)
(row 3 ...)

The problem is that the isbn field is duplicated. How can i do SELECT * JOIN queries returning only unique fields without resorting to explicitly identifying the columns to return?

Upvotes: 1

Views: 1390

Answers (2)

O. Jones
O. Jones

Reputation: 108676

Pro tip: avoid using SELECT * in software unless you have a really good reason to use it.

You have observed one reason to do this: the duplicate column problem on JOIN operations.

Other reasons:

If you enumerate your columns in your request, you control the order in which those columns appear in your result set.

Somebody can add columns to your tables without requiring changes to your software.

Retrieving columns you don't need can slow down queries, sometimes by a lot.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You need to include all the columns explicitly, but you can take short cuts. Here is an example from your tables:

select cl.*, c.name as customer_name
from customers c join
     classics cl
     on c.isbn = cl.isbn;

Upvotes: 0

Related Questions