How to Efficiently Join Tables with Comma-Separated Values?

Joining Tables with Comma-Separated Column

When joining tables where one column contains comma-separated values, finding matching records can be challenging. To solve this issue, a viable approach is to utilize the find_in_set function.

Example Query with find_in_set

Suppose we have the following schema:

CREATE TABLE tblC (
  id INT NOT NULL AUTO_INCREMENT,
  nname VARCHAR(255),
  userids VARCHAR(255),
  PRIMARY KEY (id)
);

CREATE TABLE tblB (
  id INT NOT NULL AUTO_INCREMENT,
  username VARCHAR(255),
  userid INT,
  PRIMARY KEY (id)
);

INSERT INTO tblC (nname, userids) VALUES
('new1', '1,2'),
('new2', '1,3'),
('new3', '1,4'),
('new4', '3,2'),
('new5', '5,2');

INSERT INTO tblB (username, userid) VALUES
('A', 1),
('B', 2),
('C', 3),
('D', 4),
('E', 5);

To find all the usernames when searching for "new1," we can use the following query:

SELECT *
FROM tblC AS c
JOIN tblB AS b
ON (find_in_set(b.userid, c.userids) > 0)
WHERE c.nname = 'new1';

Normalization Consideration

However, it's important to note that storing comma-separated values in a single column is considered a violation of database normalization principles. A better approach would be to have a separate junction table to hold the user associations for each row in tblC. This would eliminate the need for find_in_set and allow for more efficient queries.

Normalized Schema Sample

CREATE TABLE tblC (
  id INT NOT NULL AUTO_INCREMENT,
  nname VARCHAR(255),
  PRIMARY KEY (id)
);

CREATE TABLE tblC_user (
  c_id INT,
  userid INT,
  PRIMARY KEY (c_id, userid)
);

INSERT INTO tblC (nname) VALUES
('new1'),
('new2'),
('new3'),
('new4'),
('new5');

INSERT INTO tblC_user (c_id, userid) VALUES
(1, 1),
(1, 2),
(2, 1),
(2, 3),
(3, 1),
(3, 4),
(4, 3),
(4, 2),
(5, 5),
(5, 2);

Query with Normalized Schema

SELECT *
FROM tblC AS c
JOIN tblC_user AS cu ON (c.id = cu.c_id)
JOIN tblB AS b ON (b.userid = cu.userid)
WHERE c.nname = 'new1';

By using a normalized schema, we optimize query performance and ensure data integrity by eliminating duplicate or inconsistent relationships.

The above is the detailed content of How to Efficiently Join Tables with Comma-Separated Values?. For more information, please follow other related articles on the PHP Chinese website!