{"id":3953,"date":"2025-06-07T08:11:36","date_gmt":"2025-06-07T08:11:36","guid":{"rendered":"https:\/\/w3buddy.com\/?post_type=cposts&p=3953"},"modified":"2025-06-07T09:58:57","modified_gmt":"2025-06-07T09:58:57","slug":"sql-full-join","status":"publish","type":"cposts","link":"https:\/\/w3buddy.com\/blog\/notes\/sql\/sql-full-join\/","title":{"rendered":"SQL FULL JOIN"},"content":{"rendered":"\n

FULL JOIN<\/code> (or FULL OUTER JOIN<\/code>) returns all rows from both tables<\/strong>. If there\u2019s no match, unmatched columns return NULL<\/code>.<\/p>\n\n\n\n

\u2705 Combines the effects of LEFT JOIN<\/code> and RIGHT JOIN<\/code>.<\/p>\n\n\n\n

\ud83d\udd39 Basic Syntax<\/h2>\n\n\n\n
SELECT columns\nFROM table1\nFULL JOIN table2\n  ON table1.common_column = table2.common_column;<\/code><\/code><\/pre>\n\n\n\n
\u261d\ufe0f Some DBMS (like MySQL) don\u2019t support FULL JOIN<\/code> directly \u2014 use a workaround with UNION<\/code>.<\/p>\n\n\n\n
\ud83d\udd39 Example: List All Employees and All Departments (Matched or Not)<\/h2>\n\n\n\n
SELECT\n  e.employee_name,\n  d.department_name\nFROM employees e\nFULL JOIN departments d\n  ON e.department_id = d.department_id;<\/code><\/code><\/pre>\n\n\n\n
    \n
  • If an employee has no department \u2192 department_name<\/code> is NULL<\/code><\/li>\n\n\n\n
  • If a department has no employee \u2192 employee_name<\/code> is NULL<\/code><\/li>\n<\/ul>\n\n\n\n
    \ud83d\udd39 MySQL Workaround for FULL JOIN<\/h2>\n\n\n\n
    SELECT e.employee_name, d.department_name\nFROM employees e\nLEFT JOIN departments d ON e.department_id = d.department_id\n\nUNION\n\nSELECT e.employee_name, d.department_name\nFROM employees e\nRIGHT JOIN departments d ON e.department_id = d.department_id;<\/code><\/code><\/pre>\n\n\n\n
    \ud83e\udde0 Quick Recap<\/h2>\n\n\n\n
    Key Point<\/th>Explanation<\/th><\/tr><\/thead>
    FULL JOIN<\/code><\/td>Returns all rows from both tables<\/td><\/tr>
    No match?<\/td>Missing side columns are filled with NULL<\/code><\/td><\/tr>
    Use case<\/td>Useful when you want to show all records, matched or not<\/td><\/tr>
    MySQL note<\/td>Use LEFT JOIN<\/code> + RIGHT JOIN<\/code> with UNION<\/code> workaround<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
    \ud83e\udde9 Use FULL JOIN<\/code> to see the complete picture from both tables<\/strong>
    <\/p>\n","protected":false},"excerpt":{"rendered":"
    FULL JOIN (or FULL OUTER JOIN) returns all rows from both tables. If there\u2019s no match, unmatched columns return NULL. \u2705 Combines the effects of LEFT JOIN and RIGHT JOIN. \ud83d\udd39 Basic Syntax \u261d\ufe0f Some DBMS (like MySQL) don\u2019t support FULL JOIN directly \u2014 use a workaround with UNION. \ud83d\udd39 Example: List All Employees and […]<\/p>\n","protected":false},"template":"","meta":{"googlesitekit_rrm_CAowu461DA:productID":""},"categories":[952,979],"class_list":["post-3953","cposts","type-cposts","status-publish","hentry","category-notes","category-sql"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts\/3953","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/cposts"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=3953"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=3953"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}