[생활코딩/SQL] MySQL - 테이블 분리하기, JOIN

테이블 분리하기

테이블 분리하기 - DATABASE 2 - MySQL

• SQL문은 여기서 그냥 받아서 써라 너무 많다

 --
-- Table structure for table `author`
--


CREATE TABLE `author` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `profile` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

--
-- Dumping data for table `author`
--

INSERT INTO `author` VALUES (1,'egoing','developer');
INSERT INTO `author` VALUES (2,'duru','database administrator');
INSERT INTO `author` VALUES (3,'taeho','data scientist, developer');

--
-- Table structure for table `topic`
--

CREATE TABLE `topic` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(30) NOT NULL,
  `description` text,
  `created` datetime NOT NULL,
  `author_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

--
-- Dumping data for table `topic`
--

INSERT INTO `topic` VALUES (1,'MySQL','MySQL is...','2018-01-01 12:10:11',1);
INSERT INTO `topic` VALUES (2,'Oracle','Oracle is ...','2018-01-03 13:01:10',1);
INSERT INTO `topic` VALUES (3,'SQL Server','SQL Server is ...','2018-01-20 11:01:10',2);
INSERT INTO `topic` VALUES (4,'PostgreSQL','PostgreSQL is ...','2018-01-23 01:03:03',3);
INSERT INTO `topic` VALUES (5,'MongoDB','MongoDB is ...','2018-01-30 12:31:03',1);

---

JOIN

SELECT * FROM topic LEFT JOIN author; # 기준을 적어주지 않아서 에러가 발생함
# ERROR 1064 (42000): You have an error in your SQL syntax; 
# check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

SELECT * FROM topic LEFT JOIN author ON topic.author_id = author.id;
# topic table의 author_id와 author의 id를 맞춰서 결합해라

SELECT id, title, description, created, name, profile  FROM topic LEFT JOIN author ON topic.author_id = author.id;
# ERROR 1052 (23000): Column 'id' in field list is ambiguous

SELECT topic.id, title, description, created, name, profile FROM topic LEFT JOIN author ON topic.author_id = author.i
d;

SELECT topic.id AS topic_id, title, description, created, name, profile FROM topic LEFT JOIN author ON topic.author_i
d = author.id;