آموزش استفاده از JOIN در SQL

CREATE DATABASE joinsDB;
USE joinsDB;

CREATE TABLE products (
  productID int UNIQUE,
  productName varchar(20),
  price decimal (4,2),
  PRIMARY KEY (productID)
);

CREATE TABLE team (
  empID int UNIQUE,
  empName varchar(20),
  productSpecialty int,
  PRIMARY KEY (empID),
  FOREIGN KEY (productSpecialty) REFERENCES products (productID)
);

CREATE TABLE sales (
  saleID int UNIQUE,
  quantity int,
  productID int,
  salesperson int,
  PRIMARY KEY (saleID),
  FOREIGN KEY (productID) REFERENCES products (productID),
  FOREIGN KEY (salesperson) REFERENCES team (empID)
);

SELECT table1.column1, table2.column2
FROM table1
JOIN table2
ON condition;

SELECT team.empName, products.productName, products.price
FROM products
JOIN team
ON products.productID = team.productSpecialty;

SELECT sales.saleID, sales.quantity, products.productName, products.price
FROM sales
JOIN products
USING (productID);

SELECT sales.saleID,
team.empName,
products.productName,
(sales.quantity * products.price)
FROM products
JOIN sales USING (productID)
JOIN team ON team.productSpecialty = sales.productID
WHERE team.empID = sales.salesperson;

SELECT ...
FROM sales
JOIN team
ON sales.salesperson = team.empID;

SELECT ...
FROM sales
LEFT JOIN team
ON sales.salesperson = team.empID;

SELECT ...
FROM sales
RIGHT JOIN team
ON sales.salesperson = team.empID;

SELECT S.saleID, S.quantity,
P.productName,
(P.price * S.quantity) AS revenue
FROM sales AS S
JOIN products AS P
USING (productID);