Apply for Zend Framework Certification Training

Mysql


< Update in mysql with when then Important Mysql Query >



In MySQL, a JOIN is used to combine rows from two or more tables based on a related column.
1. INNER JOIN
Returns only the matching rows from both tables.
Syntax:
SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;
Example:
Customers
customer_id name
1 Alice
2 Bob
3 Charlie
Orders
order_id customer_id amount
101 1 500
102 2 700
103 4 300
SELECT c.name, o.order_id, o.amount
FROM Customers c
INNER JOIN Orders o
ON c.customer_id = o.customer_id;
Output:
name order_id amount
Alice 101 500
Bob 102 700
 
2. LEFT JOIN (LEFT OUTER JOIN)
Returns all rows from the left table and matching rows from the right table. If no match exists, NULL is returned.
SELECT c.name, o.order_id
FROM Customers c
LEFT JOIN Orders o
ON c.customer_id = o.customer_id;
Output:
name order_id
Alice 101
Bob 102
Charlie NULL
3. RIGHT JOIN (RIGHT OUTER JOIN)
Returns all rows from the right table and matching rows from the left table.
SELECT c.name, o.order_id
FROM Customers c
RIGHT JOIN Orders o
ON c.customer_id = o.customer_id;
Output:
name order_id
Alice 101
Bob 102
NULL 103
4. CROSS JOIN
Returns the Cartesian product (every row from the first table combined with every row from the second table).
SELECT c.name, p.product_name
FROM Customers c
CROSS JOIN Products p;
If there are 3 customers and 2 products, the result contains 6 rows.
5. SELF JOIN
A table is joined with itself.
Example: Employees and their managers.
SELECT
    e.name AS Employee,
    m.name AS Manager
FROM Employees e
LEFT JOIN Employees m
ON e.manager_id = m.employee_id;
Example with Three Tables
SELECT
    c.name,
    o.order_id,
    p.product_name
FROM Customers c
JOIN Orders o
    ON c.customer_id = o.customer_id
JOIN Products p
    ON o.product_id = p.product_id;
Summary
JOIN Type Returns
INNER JOIN Only matching rows from both tables
LEFT JOIN All rows from the left table + matching rows from the right
RIGHT JOIN All rows from the right table + matching rows from the left
CROSS JOIN Every possible combination of rows
SELF JOIN Joins a table with itself

< Update in mysql with when then Important Mysql Query >



Ask a question



  • Question: 
    {{questionlistdata.blog_question_description}}
    • Answer:
      {{answer.blog_answer_description  }}
    Replay to Question

(+91)-9910699802
Become A Member     Become A Teacher
Back to Top