How to use multi-table queries in SQL select statements

SELECT statements are used to SELECT data from a table.

Results are stored in a result table (called a result set ).

SQL SELECT syntax
SELECT column name FROM Table Name and:

SELECT * FROM table name Note: SQL statements are case-insensitive. SELECT is equivalent to select

Instance

Create table Manufacturers
(
ManfID CHAR (8) not null primary key,
ManfName VARCHAR (30) NOT NULL
)
ENGINE = INNODB;

Insert into Manufacturers
VALUES ('abc123', 'abcqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq '),
('Def456', 'defwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwww '),
('Hi789', 'hieeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee '),
('Jkl123', 'jklrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrr '),
('Mno456', 'mnotttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttt ');

Create table Parts
(
PartID smallint not null primary key,
PartName VARCHAR (30) not null,
ManfID CHAR (8) NOT NULL
)
ENGINE = INNODB;

Insert into Parts
VALUES (101, 'dvd burnerrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrr ', 'abc123 '),
(102, 'CD driveeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee '),
(103, '80-GB hard diskkkkkkkkkkkkkkkkkkkkkkkkkk ', 'mno456 '),
(104, 'mini-towerrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrr ', 'ghi789 '),
(105, 'power supplyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy ', 'def456 '),
(106, 'LCD monitorrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrr', 'mno456 '),
(107, 'zip driveeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee ', 'ghi789 '),
(108, 'floppy driveeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee '),
(109, 'network adapterrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrr ', 'def456 '),
(110, 'network hubbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb ', 'jkl123 '),
(111, 'routerrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrr ', 'mno456 '),
(112, 'sound cardddddddddddddddddddddddddddddddddddddddddddddddd', 'hi789 '),
(113, 'standard keyboarddddddddddddddddddddddddddddddd ', 'mno456 '),
(114, 'ps/2 mouseeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee '),
(115, '56-K modemmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm '),
(116, 'display adapterrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrr ', 'mno456 '),
(117, 'ide controllerrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrr ', 'def456 ');

 

SELECT PartName, ManfName
FROM Parts AS p, Manufacturers as m
WHERE p. ManfID = m. ManfID
Order by PartName;

Explain select PartName, ManfName
FROM Parts AS p, Manufacturers as m
WHERE p. ManfID = m. ManfID
Order by PartName;

With expression

Mysql tutorial> SELECT @ min_price: = MIN (price), @ max_price: = MAX (price) FROM report;
+ ------------------------ +
| @ Min_price: = MIN (price) | @ max_price: = MAX (price) |
+ ------------------------ +
| 2.34/21.29 |
+ ------------------------ +
1 row in set (0.00 sec)

Simplest select query

Mysql> SELECT firstname, lastName from employee where age> 32;
+ ----------- + ---------- +
| Firstname | lastName |
+ ----------- + ---------- +
| Mike | Harper |
+ ----------- + ---------- +
1 row in set (0.00 sec)