The INNER JOIN keyword selects records that have matching values in both tables.
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns. If there are records in the "Orders" table that do not have matches in "Customers", these orders will not be shown!
CREATE TABLE customers(
cust_id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(100),
last_name VARCHAR(100),
email VARCHAR(100)
);
CREATE TABLE orders(
id INT AUTO_INCREMENT PRIMARY KEY,
order_date DATE,
amount DECIMAL(8,2),
customer_id INT,
FOREIGN KEY(customer_id) REFERENCES customers(cust_id)
);
INSERT INTO customers (cust_id,first_name, last_name, email)
VALUES (300,'venkat', 'vlr', 'venkat.vlrtraining@gmail.com'),
(301,'praveen', 'g', 'g.praveen@gmail.com'),
(302,'lakshman', 'k', 'lakshman.k@gmail.com'),
(303,'Naveen', 'd', 'Naveend@gmail.com'),
(304,'ambani', 'e', 'e.ambani@aol.com'),
(305,'saritha', 'g', 'saritha@gmail.com'),
(500,'harika', 'p', 'harika.p@aol.com');
INSERT INTO orders (order_date, amount, customer_id)
VALUES ('2021/05/10', 199.99, 300),
('2021/09/11', 321.50, 301),
('2021/06/12', 789.67, 303),
('2021/01/03', 102.50, 304),
('2021/04/11', 850.25, 305),
('2021/03/13', 689.25, 500),
('2021/02/11', 31.50, 301),
('2021/06/12', 79.67, 303),
('2021/08/03', 102.50, 304),
('2021/07/11', 80.25, 305),
('2021/12/13', 669.25, 500);
SELECT * FROM customers, orders
WHERE customers.cust_id = orders.customer_id;
Enter password: *******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.25 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases
-> ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sakila |
| sys |
| world |
+--------------------+
6 rows in set (0.42 sec)
mysql> select * from sakila;
ERROR 1046 (3D000): No database selected
mysql> use sakila;
Database changed
mysql> select * from sakila;
ERROR 1146 (42S02): Table 'sakila.sakila' doesn't exist
mysql> show tables;
+----------------------------+
| Tables_in_sakila |
+----------------------------+
| actor |
| actor_info |
| address |
| category |
| city |
| country |
| customer |
| customer_list |
| film |
| film_actor |
| film_category |
| film_list |
| film_text |
| inventory |
| language |
| nicer_but_slower_film_list |
| payment |
| rental |
| sales_by_film_category |
| sales_by_store |
| staff |
| staff_list |
| store |
+----------------------------+
23 rows in set (0.22 sec)
mysql> select * from actor;
+----------+-------------+--------------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+-------------+--------------+---------------------+
| 1 | PENELOPE | GUINESS | 2006-02-15 04:34:33 |
| 2 | NICK | WAHLBERG | 2006-02-15 04:34:33 |
| 3 | ED | CHASE | 2006-02-15 04:34:33 |
| 4 | JENNIFER | DAVIS | 2006-02-15 04:34:33 |
| 5 | JOHNNY | LOLLOBRIGIDA | 2006-02-15 04:34:33 |
| 6 | BETTE | NICHOLSON | 2006-02-15 04:34:33 |
| 7 | GRACE | MOSTEL | 2006-02-15 04:34:33 |
| 8 | MATTHEW | JOHANSSON | 2006-02-15 04:34:33 |
| 9 | JOE | SWANK | 2006-02-15 04:34:33 |
| 10 | CHRISTIAN | GABLE | 2006-02-15 04:34:33 |
| 11 | ZERO | CAGE | 2006-02-15 04:34:33 |
| 12 | KARL | BERRY | 2006-02-15 04:34:33 |
| 13 | UMA | WOOD | 2006-02-15 04:34:33 |
| 14 | VIVIEN | BERGEN | 2006-02-15 04:34:33 |
| 15 | CUBA | OLIVIER | 2006-02-15 04:34:33 |
| 16 | FRED | COSTNER | 2006-02-15 04:34:33 |
| 17 | HELEN | VOIGHT | 2006-02-15 04:34:33 |
| 18 | DAN | TORN | 2006-02-15 04:34:33 |
| 19 | BOB | FAWCETT | 2006-02-15 04:34:33 |
| 20 | LUCILLE | TRACY | 2006-02-15 04:34:33 |
| 21 | KIRSTEN | PALTROW | 2006-02-15 04:34:33 |
| 22 | ELVIS | MARX | 2006-02-15 04:34:33 |
| 23 | SANDRA | KILMER | 2006-02-15 04:34:33 |
| 24 | CAMERON | STREEP | 2006-02-15 04:34:33 |
| 25 | KEVIN | BLOOM | 2006-02-15 04:34:33 |
| 26 | RIP | CRAWFORD | 2006-02-15 04:34:33 |
| 27 | JULIA | MCQUEEN | 2006-02-15 04:34:33 |
| 28 | WOODY | HOFFMAN | 2006-02-15 04:34:33 |
| 29 | ALEC | WAYNE | 2006-02-15 04:34:33 |
| 30 | SANDRA | PECK | 2006-02-15 04:34:33 |
| 31 | SISSY | SOBIESKI | 2006-02-15 04:34:33 |
| 32 | TIM | HACKMAN | 2006-02-15 04:34:33 |
| 33 | MILLA | PECK | 2006-02-15 04:34:33 |
| 34 | AUDREY | OLIVIER | 2006-02-15 04:34:33 |
| 35 | JUDY | DEAN | 2006-02-15 04:34:33 |
| 36 | BURT | DUKAKIS | 2006-02-15 04:34:33 |
| 37 | VAL | BOLGER | 2006-02-15 04:34:33 |
| 38 | TOM | MCKELLEN | 2006-02-15 04:34:33 |
| 39 | GOLDIE | BRODY | 2006-02-15 04:34:33 |
| 40 | JOHNNY | CAGE | 2006-02-15 04:34:33 |
| 41 | JODIE | DEGENERES | 2006-02-15 04:34:33 |
| 42 | TOM | MIRANDA | 2006-02-15 04:34:33 |
| 43 | KIRK | JOVOVICH | 2006-02-15 04:34:33 |
| 44 | NICK | STALLONE | 2006-02-15 04:34:33 |
| 45 | REESE | KILMER | 2006-02-15 04:34:33 |
| 46 | PARKER | GOLDBERG | 2006-02-15 04:34:33 |
| 47 | JULIA | BARRYMORE | 2006-02-15 04:34:33 |
| 48 | FRANCES | DAY-LEWIS | 2006-02-15 04:34:33 |
| 49 | ANNE | CRONYN | 2006-02-15 04:34:33 |
| 50 | NATALIE | HOPKINS | 2006-02-15 04:34:33 |
| 51 | GARY | PHOENIX | 2006-02-15 04:34:33 |
| 52 | CARMEN | HUNT | 2006-02-15 04:34:33 |
| 53 | MENA | TEMPLE | 2006-02-15 04:34:33 |
| 54 | PENELOPE | PINKETT | 2006-02-15 04:34:33 |
| 55 | FAY | KILMER | 2006-02-15 04:34:33 |
| 56 | DAN | HARRIS | 2006-02-15 04:34:33 |
| 57 | JUDE | CRUISE | 2006-02-15 04:34:33 |
| 58 | CHRISTIAN | AKROYD | 2006-02-15 04:34:33 |
| 59 | DUSTIN | TAUTOU | 2006-02-15 04:34:33 |
| 60 | HENRY | BERRY | 2006-02-15 04:34:33 |
| 61 | CHRISTIAN | NEESON | 2006-02-15 04:34:33 |
| 62 | JAYNE | NEESON | 2006-02-15 04:34:33 |
| 63 | CAMERON | WRAY | 2006-02-15 04:34:33 |
| 64 | RAY | JOHANSSON | 2006-02-15 04:34:33 |
| 65 | ANGELA | HUDSON | 2006-02-15 04:34:33 |
| 66 | MARY | TANDY | 2006-02-15 04:34:33 |
| 67 | JESSICA | BAILEY | 2006-02-15 04:34:33 |
| 68 | RIP | WINSLET | 2006-02-15 04:34:33 |
| 69 | KENNETH | PALTROW | 2006-02-15 04:34:33 |
| 70 | MICHELLE | MCCONAUGHEY | 2006-02-15 04:34:33 |
| 71 | ADAM | GRANT | 2006-02-15 04:34:33 |
| 72 | SEAN | WILLIAMS | 2006-02-15 04:34:33 |
| 73 | GARY | PENN | 2006-02-15 04:34:33 |
| 74 | MILLA | KEITEL | 2006-02-15 04:34:33 |
| 75 | BURT | POSEY | 2006-02-15 04:34:33 |
| 76 | ANGELINA | ASTAIRE | 2006-02-15 04:34:33 |
| 77 | CARY | MCCONAUGHEY | 2006-02-15 04:34:33 |
| 78 | GROUCHO | SINATRA | 2006-02-15 04:34:33 |
| 79 | MAE | HOFFMAN | 2006-02-15 04:34:33 |
| 80 | RALPH | CRUZ | 2006-02-15 04:34:33 |
| 81 | SCARLETT | DAMON | 2006-02-15 04:34:33 |
| 82 | WOODY | JOLIE | 2006-02-15 04:34:33 |
| 83 | BEN | WILLIS | 2006-02-15 04:34:33 |
| 84 | JAMES | PITT | 2006-02-15 04:34:33 |
| 85 | MINNIE | ZELLWEGER | 2006-02-15 04:34:33 |
| 86 | GREG | CHAPLIN | 2006-02-15 04:34:33 |
| 87 | SPENCER | PECK | 2006-02-15 04:34:33 |
| 88 | KENNETH | PESCI | 2006-02-15 04:34:33 |
| 89 | CHARLIZE | DENCH | 2006-02-15 04:34:33 |
| 90 | SEAN | GUINESS | 2006-02-15 04:34:33 |
| 91 | CHRISTOPHER | BERRY | 2006-02-15 04:34:33 |
| 92 | KIRSTEN | AKROYD | 2006-02-15 04:34:33 |
| 93 | ELLEN | PRESLEY | 2006-02-15 04:34:33 |
| 94 | KENNETH | TORN | 2006-02-15 04:34:33 |
| 95 | DARYL | WAHLBERG | 2006-02-15 04:34:33 |
| 96 | GENE | WILLIS | 2006-02-15 04:34:33 |
| 97 | MEG | HAWKE | 2006-02-15 04:34:33 |
| 98 | CHRIS | BRIDGES | 2006-02-15 04:34:33 |
| 99 | JIM | MOSTEL | 2006-02-15 04:34:33 |
| 100 | SPENCER | DEPP | 2006-02-15 04:34:33 |
| 101 | SUSAN | DAVIS | 2006-02-15 04:34:33 |
| 102 | WALTER | TORN | 2006-02-15 04:34:33 |
| 103 | MATTHEW | LEIGH | 2006-02-15 04:34:33 |
| 104 | PENELOPE | CRONYN | 2006-02-15 04:34:33 |
| 105 | SIDNEY | CROWE | 2006-02-15 04:34:33 |
| 106 | GROUCHO | DUNST | 2006-02-15 04:34:33 |
| 107 | GINA | DEGENERES | 2006-02-15 04:34:33 |
| 108 | WARREN | NOLTE | 2006-02-15 04:34:33 |
| 109 | SYLVESTER | DERN | 2006-02-15 04:34:33 |
| 110 | SUSAN | DAVIS | 2006-02-15 04:34:33 |
| 111 | CAMERON | ZELLWEGER | 2006-02-15 04:34:33 |
| 112 | RUSSELL | BACALL | 2006-02-15 04:34:33 |
| 113 | MORGAN | HOPKINS | 2006-02-15 04:34:33 |
| 114 | MORGAN | MCDORMAND | 2006-02-15 04:34:33 |
| 115 | HARRISON | BALE | 2006-02-15 04:34:33 |
| 116 | DAN | STREEP | 2006-02-15 04:34:33 |
| 117 | RENEE | TRACY | 2006-02-15 04:34:33 |
| 118 | CUBA | ALLEN | 2006-02-15 04:34:33 |
| 119 | WARREN | JACKMAN | 2006-02-15 04:34:33 |
| 120 | PENELOPE | MONROE | 2006-02-15 04:34:33 |
| 121 | LIZA | BERGMAN | 2006-02-15 04:34:33 |
| 122 | SALMA | NOLTE | 2006-02-15 04:34:33 |
| 123 | JULIANNE | DENCH | 2006-02-15 04:34:33 |
| 124 | SCARLETT | BENING | 2006-02-15 04:34:33 |
| 125 | ALBERT | NOLTE | 2006-02-15 04:34:33 |
| 126 | FRANCES | TOMEI | 2006-02-15 04:34:33 |
| 127 | KEVIN | GARLAND | 2006-02-15 04:34:33 |
| 128 | CATE | MCQUEEN | 2006-02-15 04:34:33 |
| 129 | DARYL | CRAWFORD | 2006-02-15 04:34:33 |
| 130 | GRETA | KEITEL | 2006-02-15 04:34:33 |
| 131 | JANE | JACKMAN | 2006-02-15 04:34:33 |
| 132 | ADAM | HOPPER | 2006-02-15 04:34:33 |
| 133 | RICHARD | PENN | 2006-02-15 04:34:33 |
| 134 | GENE | HOPKINS | 2006-02-15 04:34:33 |
| 135 | RITA | REYNOLDS | 2006-02-15 04:34:33 |
| 136 | ED | MANSFIELD | 2006-02-15 04:34:33 |
| 137 | MORGAN | WILLIAMS | 2006-02-15 04:34:33 |
| 138 | LUCILLE | DEE | 2006-02-15 04:34:33 |
| 139 | EWAN | GOODING | 2006-02-15 04:34:33 |
| 140 | WHOOPI | HURT | 2006-02-15 04:34:33 |
| 141 | CATE | HARRIS | 2006-02-15 04:34:33 |
| 142 | JADA | RYDER | 2006-02-15 04:34:33 |
| 143 | RIVER | DEAN | 2006-02-15 04:34:33 |
| 144 | ANGELA | WITHERSPOON | 2006-02-15 04:34:33 |
| 145 | KIM | ALLEN | 2006-02-15 04:34:33 |
| 146 | ALBERT | JOHANSSON | 2006-02-15 04:34:33 |
| 147 | FAY | WINSLET | 2006-02-15 04:34:33 |
| 148 | EMILY | DEE | 2006-02-15 04:34:33 |
| 149 | RUSSELL | TEMPLE | 2006-02-15 04:34:33 |
| 150 | JAYNE | NOLTE | 2006-02-15 04:34:33 |
| 151 | GEOFFREY | HESTON | 2006-02-15 04:34:33 |
| 152 | BEN | HARRIS | 2006-02-15 04:34:33 |
| 153 | MINNIE | KILMER | 2006-02-15 04:34:33 |
| 154 | MERYL | GIBSON | 2006-02-15 04:34:33 |
| 155 | IAN | TANDY | 2006-02-15 04:34:33 |
| 156 | FAY | WOOD | 2006-02-15 04:34:33 |
| 157 | GRETA | MALDEN | 2006-02-15 04:34:33 |
| 158 | VIVIEN | BASINGER | 2006-02-15 04:34:33 |
| 159 | LAURA | BRODY | 2006-02-15 04:34:33 |
| 160 | CHRIS | DEPP | 2006-02-15 04:34:33 |
| 161 | HARVEY | HOPE | 2006-02-15 04:34:33 |
| 162 | OPRAH | KILMER | 2006-02-15 04:34:33 |
| 163 | CHRISTOPHER | WEST | 2006-02-15 04:34:33 |
| 164 | HUMPHREY | WILLIS | 2006-02-15 04:34:33 |
| 165 | AL | GARLAND | 2006-02-15 04:34:33 |
| 166 | NICK | DEGENERES | 2006-02-15 04:34:33 |
| 167 | LAURENCE | BULLOCK | 2006-02-15 04:34:33 |
| 168 | WILL | WILSON | 2006-02-15 04:34:33 |
| 169 | KENNETH | HOFFMAN | 2006-02-15 04:34:33 |
| 170 | MENA | HOPPER | 2006-02-15 04:34:33 |
| 171 | OLYMPIA | PFEIFFER | 2006-02-15 04:34:33 |
| 172 | GROUCHO | WILLIAMS | 2006-02-15 04:34:33 |
| 173 | ALAN | DREYFUSS | 2006-02-15 04:34:33 |
| 174 | MICHAEL | BENING | 2006-02-15 04:34:33 |
| 175 | WILLIAM | HACKMAN | 2006-02-15 04:34:33 |
| 176 | JON | CHASE | 2006-02-15 04:34:33 |
| 177 | GENE | MCKELLEN | 2006-02-15 04:34:33 |
| 178 | LISA | MONROE | 2006-02-15 04:34:33 |
| 179 | ED | GUINESS | 2006-02-15 04:34:33 |
| 180 | JEFF | SILVERSTONE | 2006-02-15 04:34:33 |
| 181 | MATTHEW | CARREY | 2006-02-15 04:34:33 |
| 182 | DEBBIE | AKROYD | 2006-02-15 04:34:33 |
| 183 | RUSSELL | CLOSE | 2006-02-15 04:34:33 |
| 184 | HUMPHREY | GARLAND | 2006-02-15 04:34:33 |
| 185 | MICHAEL | BOLGER | 2006-02-15 04:34:33 |
| 186 | JULIA | ZELLWEGER | 2006-02-15 04:34:33 |
| 187 | RENEE | BALL | 2006-02-15 04:34:33 |
| 188 | ROCK | DUKAKIS | 2006-02-15 04:34:33 |
| 189 | CUBA | BIRCH | 2006-02-15 04:34:33 |
| 190 | AUDREY | BAILEY | 2006-02-15 04:34:33 |
| 191 | GREGORY | GOODING | 2006-02-15 04:34:33 |
| 192 | JOHN | SUVARI | 2006-02-15 04:34:33 |
| 193 | BURT | TEMPLE | 2006-02-15 04:34:33 |
| 194 | MERYL | ALLEN | 2006-02-15 04:34:33 |
| 195 | JAYNE | SILVERSTONE | 2006-02-15 04:34:33 |
| 196 | BELA | WALKEN | 2006-02-15 04:34:33 |
| 197 | REESE | WEST | 2006-02-15 04:34:33 |
| 198 | MARY | KEITEL | 2006-02-15 04:34:33 |
| 199 | JULIA | FAWCETT | 2006-02-15 04:34:33 |
| 200 | THORA | TEMPLE | 2006-02-15 04:34:33 |
+----------+-------------+--------------+---------------------+
200 rows in set (0.21 sec)
mysql> create database ram;
Query OK, 1 row affected (0.46 sec)
mysql> use ram;
Database changed
mysql> CREATE TABLE customers(
-> cust_id INT AUTO_INCREMENT PRIMARY KEY,
-> first_name VARCHAR(100),
-> last_name VARCHAR(100),
-> email VARCHAR(100)
-> );
Query OK, 0 rows affected (2.17 sec)
mysql>
mysql>
mysql> CREATE TABLE orders(
-> id INT AUTO_INCREMENT PRIMARY KEY,
-> order_date DATE,
-> amount DECIMAL(8,2),
-> customer_id INT,
-> FOREIGN KEY(customer_id) REFERENCES customers(cust_id)
-> );
Query OK, 0 rows affected (0.63 sec)
mysql>
mysql> INSERT INTO customers (id,first_name, last_name, email)
-> VALUES (300,'venkat', 'vlr', 'venkat.vlrtraining@gmail.com'),
-> (301,'praveen', 'g', 'g.praveen@gmail.com'),
-> (302,'lakshman', 'k', 'lakshman.k@gmail.com'),
-> (303,'Naveen', 'd', 'Naveend@gmail.com'),
-> (304,'ambani', 'e', 'e.ambani@aol.com')
-> (305,'saritha', 'g', 'saritha@gmail.com'),
-> (500,'harika', 'p', 'harika.p@aol.com');
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 '(305,'saritha', 'g', 'saritha@gmail.com'),
(500,'harika', 'p', 'harika.p@' at line 7
mysql>
mysql> INSERT INTO orders (order_date, amount, customer_id)
-> VALUES ('2021/05/10', 199.99, 300),
-> ('2021/09/11', 321.50, 301),
-> ('2021/06/12', 789.67, 303),
-> ('2021/01/03', 102.50, 304),
-> ('2021/04/11', 850.25, 305),
-> ('2021/03/13', 689.25, 500),
-> ('2021/02/11', 31.50, 301),
-> ('2021/06/12', 79.67, 303),
-> ('2021/08/03', 102.50, 304),
-> ('2021/07/11', 80.25, 305),
-> ('2021/12/13', 669.25, 500);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`ram`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`cust_id`))
mysql> select * from customers;
Empty set (0.00 sec)
mysql>
mysql> INSERT INTO customers (cust_id,first_name, last_name, email)
-> VALUES (300,'venkat', 'vlr', 'venkat.vlrtraining@gmail.com'),
-> (301,'praveen', 'g', 'g.praveen@gmail.com'),
-> (302,'lakshman', 'k', 'lakshman.k@gmail.com'),
-> (303,'Naveen', 'd', 'Naveend@gmail.com'),
-> (304,'ambani', 'e', 'e.ambani@aol.com')
-> (305,'saritha', 'g', 'saritha@gmail.com'),
-> (500,'harika', 'p', 'harika.p@aol.com');
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 '(305,'saritha', 'g', 'saritha@gmail.com'),
(500,'harika', 'p', 'harika.p@' at line 7
mysql> INSERT INTO customers (cust_id,first_name, last_name, email)
-> VALUES (300,'venkat', 'vlr', 'venkat.vlrtraining@gmail.com'),
-> (301,'praveen', 'g', 'g.praveen@gmail.com'),
-> (302,'lakshman', 'k', 'lakshman.k@gmail.com'),
-> (303,'Naveen', 'd', 'Naveend@gmail.com'),
-> (304,'ambani', 'e', 'e.ambani@aol.com'),
-> (305,'saritha', 'g', 'saritha@gmail.com'),
-> (500,'harika', 'p', 'harika.p@aol.com');
Query OK, 7 rows affected (0.19 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> INSERT INTO orders (order_date, amount, customer_id)
-> VALUES ('2021/05/10', 199.99, 300),
-> ('2021/09/11', 321.50, 301),
-> ('2021/06/12', 789.67, 303),
-> ('2021/01/03', 102.50, 304),
-> ('2021/04/11', 850.25, 305),
-> ('2021/03/13', 689.25, 500),
-> ('2021/02/11', 31.50, 301),
-> ('2021/06/12', 79.67, 303),
-> ('2021/08/03', 102.50, 304),
-> ('2021/07/11', 80.25, 305),
-> ('2021/12/13', 669.25, 500);
Query OK, 11 rows affected (0.22 sec)
Records: 11 Duplicates: 0 Warnings: 0
mysql> select * from customers;
+---------+------------+-----------+------------------------------+
| cust_id | first_name | last_name | email |
+---------+------------+-----------+------------------------------+
| 300 | venkat | vlr | venkat.vlrtraining@gmail.com |
| 301 | praveen | g | g.praveen@gmail.com |
| 302 | lakshman | k | lakshman.k@gmail.com |
| 303 | Naveen | d | Naveend@gmail.com |
| 304 | ambani | e | e.ambani@aol.com |
| 305 | saritha | g | saritha@gmail.com |
| 500 | harika | p | harika.p@aol.com |
+---------+------------+-----------+------------------------------+
7 rows in set (0.00 sec)
mysql> select * from orders;
+----+------------+--------+-------------+
| id | order_date | amount | customer_id |
+----+------------+--------+-------------+
| 12 | 2021-05-10 | 199.99 | 300 |
| 13 | 2021-09-11 | 321.50 | 301 |
| 14 | 2021-06-12 | 789.67 | 303 |
| 15 | 2021-01-03 | 102.50 | 304 |
| 16 | 2021-04-11 | 850.25 | 305 |
| 17 | 2021-03-13 | 689.25 | 500 |
| 18 | 2021-02-11 | 31.50 | 301 |
| 19 | 2021-06-12 | 79.67 | 303 |
| 20 | 2021-08-03 | 102.50 | 304 |
| 21 | 2021-07-11 | 80.25 | 305 |
| 22 | 2021-12-13 | 669.25 | 500 |
+----+------------+--------+-------------+
11 rows in set (0.00 sec)
mysql> select * from customers,orders where customers.cust_id=orders.customer_id;
+---------+------------+-----------+------------------------------+----+------------+--------+-------------+
| cust_id | first_name | last_name | email | id | order_date | amount | customer_id |
+---------+------------+-----------+------------------------------+----+------------+--------+-------------+
| 300 | venkat | vlr | venkat.vlrtraining@gmail.com | 12 | 2021-05-10 | 199.99 | 300 |
| 301 | praveen | g | g.praveen@gmail.com | 13 | 2021-09-11 | 321.50 | 301 |
| 301 | praveen | g | g.praveen@gmail.com | 18 | 2021-02-11 | 31.50 | 301 |
| 303 | Naveen | d | Naveend@gmail.com | 14 | 2021-06-12 | 789.67 | 303 |
| 303 | Naveen | d | Naveend@gmail.com | 19 | 2021-06-12 | 79.67 | 303 |
| 304 | ambani | e | e.ambani@aol.com | 15 | 2021-01-03 | 102.50 | 304 |
| 304 | ambani | e | e.ambani@aol.com | 20 | 2021-08-03 | 102.50 | 304 |
| 305 | saritha | g | saritha@gmail.com | 16 | 2021-04-11 | 850.25 | 305 |
| 305 | saritha | g | saritha@gmail.com | 21 | 2021-07-11 | 80.25 | 305 |
| 500 | harika | p | harika.p@aol.com | 17 | 2021-03-13 | 689.25 | 500 |
| 500 | harika | p | harika.p@aol.com | 22 | 2021-12-13 | 669.25 | 500 |
+---------+------------+-----------+------------------------------+----+------------+--------+-------------+
11 rows in set (0.00 sec)
mysql> select * from customers inner join orders on INSERT INTO orders (order_date, amount, customer_id)
-> VALUES ('2021/05/10', 199.99, 300),
-> ('2021/09/11', 321.50, 301),
-> ('2021/06/12', 789.67, 303),
-> ('2021/01/03', 102.50, 304),
-> ('2021/04/11', 850.25, 305),
-> ('2021/03/13', 689.25, 500),
-> ('2021/02/11', 31.50, 301),
-> ('2021/06/12', 79.67, 303),
-> ('2021/08/03', 102.50, 304),
-> ('2021/07/11', 80.25, 305),
-> ^X
-> ;;;;
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 'INTO orders (order_date, amount, customer_id)
VALUES ('2021/05/10', 199.99, 300)' at line 1
ERROR:
No query specified
ERROR:
No query specified
ERROR:
No query specified
mysql> INSERT INTO orders (order_date, amount, customer_id)
-> VALUES ('2021/05/10', 199.99, 300),
-> ('2021/09/11', 321.50, 301),
-> ('2021/06/12', 789.67, 303),
-> ('2021/01/03', 102.50, 304),
-> ('2021/04/11', 850.25, 305),
-> ('2021/03/13', 689.25, 500),
-> ('2021/02/11', 31.50, 301),
-> ('2021/06/12', 79.67, 303),
-> ('2021/08/03', 102.50, 304),
-> ('2021/07/11', 80.25, 305),
-> ('2021/12/13', 669.25, 500)qq
-> qwerq;;
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 'qq
qwerq' at line 12
ERROR:
No query specified
mysql> select * from customers inner join orders on customers.cust_id=orders.customer_id;
+---------+------------+-----------+------------------------------+----+------------+--------+-------------+
| cust_id | first_name | last_name | email | id | order_date | amount | customer_id |
+---------+------------+-----------+------------------------------+----+------------+--------+-------------+
| 300 | venkat | vlr | venkat.vlrtraining@gmail.com | 12 | 2021-05-10 | 199.99 | 300 |
| 301 | praveen | g | g.praveen@gmail.com | 13 | 2021-09-11 | 321.50 | 301 |
| 301 | praveen | g | g.praveen@gmail.com | 18 | 2021-02-11 | 31.50 | 301 |
| 303 | Naveen | d | Naveend@gmail.com | 14 | 2021-06-12 | 789.67 | 303 |
| 303 | Naveen | d | Naveend@gmail.com | 19 | 2021-06-12 | 79.67 | 303 |
| 304 | ambani | e | e.ambani@aol.com | 15 | 2021-01-03 | 102.50 | 304 |
| 304 | ambani | e | e.ambani@aol.com | 20 | 2021-08-03 | 102.50 | 304 |
| 305 | saritha | g | saritha@gmail.com | 16 | 2021-04-11 | 850.25 | 305 |
| 305 | saritha | g | saritha@gmail.com | 21 | 2021-07-11 | 80.25 | 305 |
| 500 | harika | p | harika.p@aol.com | 17 | 2021-03-13 | 689.25 | 500 |
| 500 | harika | p | harika.p@aol.com | 22 | 2021-12-13 | 669.25 | 500 |
+---------+------------+-----------+------------------------------+----+------------+--------+-------------+
11 rows in set (0.00 sec)
mysql> select cust_id,id as orderid from customers inner join orders on customers.cust_id=orders.customer_id;
+---------+---------+
| cust_id | orderid |
+---------+---------+
| 300 | 12 |
| 301 | 13 |
| 301 | 18 |
| 303 | 14 |
| 303 | 19 |
| 304 | 15 |
| 304 | 20 |
| 305 | 16 |
| 305 | 21 |
| 500 | 17 |
| 500 | 22 |
+---------+---------+
11 rows in set (0.00 sec)
mysql>
No comments:
Post a Comment
Note: only a member of this blog may post a comment.