Mysql内连接_INNER JOIN

一、连接同一数据库的两张表

SELECT *
FROM orders
-- 内连接时INNER可以省略
INNER JOIN customers ON  orders.customer_id=customers.customer_id

这样会将orders表和customers表内容全部显示出来，左边是orders表

 

SELECT orders.customer_id,order_id,first_name,last_name,phone    # 有两列customer_id， 需指明选中哪一个
FROM orders
-- 内连接时INNER可以省略
INNER JOIN customers ON  orders.customer_id=customers.customer_id

表的名字后面紧跟字符，该字符可指替表——使用该方法后原来的表名将不再适用

SELECT o.customer_id,order_id,first_name,last_name,phone
FROM orders o # o代替orders，保存美观
-- 内连接时INNER可以省略
INNER JOIN customers c ON o.customer_id=c.customer_id

二、跨数据库连接两张表

将sql_store数据库中的order_items表和sql_inventory数据库中的products表连接起来，都包含product_id

 

USE sql_store;
SELECT *
FROM sql_inventory.products p    # 先展示此表，只需要给不在当前数据库中的表加前缀
JOIN order_items oi
    ON p.product_id=oi.product_id

USE sql_store;
SELECT *
FROM order_items oi    # 先展示此表
JOIN sql_inventory.products p
    ON p.product_id=oi.product_id

USE sql_inventory;
SELECT *
FROM sql_store.order_items oi    # 先展示此表
JOIN products p
    ON p.product_id=oi.product_id

三、自连接-自己连接自己，例如员工和管理人员在同一表中，查找他们的关系

USE sql_hr;
SELECT
    e.employee_id,
    e.first_name AS employee,
    m.first_name AS manager
FROM employees e
JOIN employees m ON e.reports_to=m.employee_id

 

四、连接同一数据库的多张表

USE sql_store;
SELECT order_id,order_date,first_name,last_name,os.name AS status
FROM orders o
JOIN customers c ON c.customer_id=o.customer_id
JOIN order_statuses os ON os.order_status_id=o.status

USE sql_invoicing;
SELECT
    c.name,
    pm.name AS method,
    p.date,p.amount
FROM clients c
JOIN payments p
    ON p.client_id=c.client_id
JOIN payment_methods pm
    ON pm.payment_method_id=p.payment_method

五、复合连接-连接有多个主键的表

USE sql_store;
SELECT *
FROM order_items oi
JOIN order_item_notes oin
    ON oi.order_id=oin.order_Id
    AND oi.product_id=oin.product_id

六、隐式语法内连接-不建议使用-强烈建议使用显示语法JOIN

USE sql_store;
SELECT *
FROM customers c,orders o
WHERE c.customer_id=o.customer_id