INNER JOIN vs OUTER JOIN

What is an INNER JOIN?

[{"selector":"#anim-7ef90348-7382-4170-8c07-21707ef5aaee","keyframes":{"opacity":[0,1]},"delay":200,"duration":800,"easing":"cubic-bezier(0.2, 0.6, 0.0, 1)","fill":"both"}] [{"selector":"#anim-65670827-068c-460a-b97f-5b1696500347","keyframes":{"transform":["translate3d(0px, 336.96137%, 0)","translate3d(0px, 0px, 0)"]},"delay":200,"duration":800,"easing":"cubic-bezier(0.2, 0.6, 0.0, 1)","fill":"both"}] [{"selector":"#anim-c2fd9c47-5419-49b5-829d-400aab87dfc9","keyframes":{"opacity":[0,1]},"delay":0,"duration":1200,"easing":"cubic-bezier(0.2, 0.6, 0.0, 1)","fill":"both"}] [{"selector":"#anim-a1e4ac3f-76f8-46bd-8cf7-079e079e8df3","keyframes":{"transform":["translate3d(0px, -199.53147%, 0)","translate3d(0px, 0px, 0)"]},"delay":0,"duration":1200,"easing":"cubic-bezier(0.2, 0.6, 0.0, 1)","fill":"both"}] [{"selector":"#anim-1bbab0d0-896b-4914-bf30-2c0d459beaea","keyframes":{"opacity":[0,1]},"delay":300,"duration":1200,"easing":"cubic-bezier(0.2, 0.6, 0.0, 1)","fill":"both"}] [{"selector":"#anim-55deb5e5-5a99-44bd-80b6-14ef1a45a013","keyframes":{"transform":["translate3d(0px, 388.10925%, 0)","translate3d(0px, 0px, 0)"]},"delay":300,"duration":1200,"easing":"cubic-bezier(0.2, 0.6, 0.0, 1)","fill":"both"}] [{"selector":"#anim-d319f52d-f565-4265-b3fd-08e9a6fc2a7c","keyframes":{"opacity":[0,1]},"delay":500,"duration":1000,"easing":"cubic-bezier(0.2, 0.6, 0.0, 1)","fill":"both"}] [{"selector":"#anim-9d248eb7-86a2-4289-832f-1051ecca6b46","keyframes":{"transform":["translate3d(0px, 538.29419%, 0)","translate3d(0px, 0px, 0)"]},"delay":500,"duration":1000,"easing":"cubic-bezier(0.2, 0.6, 0.0, 1)","fill":"both"}] [{"selector":"#anim-6cca0e5b-036e-4ad7-bb1e-404ec7438ed4","keyframes":{"opacity":[0,1]},"delay":550,"duration":1000,"easing":"cubic-bezier(0.2, 0.6, 0.0, 1)","fill":"both"}] [{"selector":"#anim-560047ce-14c9-480c-9f2d-f39505cea41c","keyframes":{"transform":["translate3d(0px, 541.56395%, 0)","translate3d(0px, 0px, 0)"]},"delay":550,"duration":1000,"easing":"cubic-bezier(0.2, 0.6, 0.0, 1)","fill":"both"}] Returns only rows with matching values from both tables, showing common data. Read More

What is an OUTER JOIN?

[{"selector":"#anim-300cfb2b-6085-4eb8-8d68-55476cb98160","keyframes":{"opacity":[0,1]},"delay":200,"duration":800,"easing":"cubic-bezier(0.2, 0.6, 0.0, 1)","fill":"both"}] [{"selector":"#anim-a32d966b-d2df-41a6-b736-4f91c5d27440","keyframes":{"transform":["translate3d(0px, 336.96137%, 0)","translate3d(0px, 0px, 0)"]},"delay":200,"duration":800,"easing":"cubic-bezier(0.2, 0.6, 0.0, 1)","fill":"both"}] [{"selector":"#anim-f5e9c443-883d-4752-9243-72b0d03c63fb","keyframes":{"opacity":[0,1]},"delay":300,"duration":1200,"easing":"cubic-bezier(0.2, 0.6, 0.0, 1)","fill":"both"}] [{"selector":"#anim-6e6f84e3-8e21-4022-9545-35eb3b2fac33","keyframes":{"transform":["translate3d(0px, 388.10925%, 0)","translate3d(0px, 0px, 0)"]},"delay":300,"duration":1200,"easing":"cubic-bezier(0.2, 0.6, 0.0, 1)","fill":"both"}] [{"selector":"#anim-1d0b0fc0-212f-4f59-b568-33912bc7c9d9","keyframes":{"opacity":[0,1]},"delay":500,"duration":1000,"easing":"cubic-bezier(0.2, 0.6, 0.0, 1)","fill":"both"}] [{"selector":"#anim-c19e848f-25c1-4675-8c5c-5f8ba78a5780","keyframes":{"transform":["translate3d(0px, 488.29419%, 0)","translate3d(0px, 0px, 0)"]},"delay":500,"duration":1000,"easing":"cubic-bezier(0.2, 0.6, 0.0, 1)","fill":"both"}] [{"selector":"#anim-32cac687-35a9-4913-8382-bf4534fe1315","keyframes":{"opacity":[0,1]},"delay":550,"duration":1000,"easing":"cubic-bezier(0.2, 0.6, 0.0, 1)","fill":"both"}] [{"selector":"#anim-2fd3362d-86d3-4665-9dee-f1234d00b6d9","keyframes":{"transform":["translate3d(0px, 489.71213%, 0)","translate3d(0px, 0px, 0)"]},"delay":550,"duration":1000,"easing":"cubic-bezier(0.2, 0.6, 0.0, 1)","fill":"both"}] [{"selector":"#anim-84893853-8c46-46aa-908e-3cf66ef0cf9c","keyframes":{"opacity":[0,1]},"delay":0,"duration":1200,"easing":"cubic-bezier(0.2, 0.6, 0.0, 1)","fill":"both"}] [{"selector":"#anim-483ee7ef-d76b-4cca-8515-32bc36fe0c03","keyframes":{"transform":["translate3d(0px, -167.55766%, 0)","translate3d(0px, 0px, 0)"]},"delay":0,"duration":1200,"easing":"cubic-bezier(0.2, 0.6, 0.0, 1)","fill":"both"}] Returns matching rows plus non-matching rows from one or both tables with NULLs. Read More

Types of Outer Joins

[{"selector":"#anim-ec23ac82-f549-4f66-87a2-a8c649277ecd","keyframes":{"opacity":[0,1]},"delay":200,"duration":800,"easing":"cubic-bezier(0.2, 0.6, 0.0, 1)","fill":"both"}] [{"selector":"#anim-d971cdcb-a1ee-4e09-9519-37fe3f7d1a4e","keyframes":{"transform":["translate3d(0px, 337.98176%, 0)","translate3d(0px, 0px, 0)"]},"delay":200,"duration":800,"easing":"cubic-bezier(0.2, 0.6, 0.0, 1)","fill":"both"}] [{"selector":"#anim-ee24807a-d96c-4ce8-bdae-8f43760fd9cc","keyframes":{"opacity":[0,1]},"delay":300,"duration":1200,"easing":"cubic-bezier(0.2, 0.6, 0.0, 1)","fill":"both"}] [{"selector":"#anim-71cb6b99-1bc8-4af1-96b8-1d7046123f8e","keyframes":{"transform":["translate3d(0px, 289.76616%, 0)","translate3d(0px, 0px, 0)"]},"delay":300,"duration":1200,"easing":"cubic-bezier(0.2, 0.6, 0.0, 1)","fill":"both"}] [{"selector":"#anim-f89e28e8-3f68-4116-8c6b-90b38172f157","keyframes":{"opacity":[0,1]},"delay":0,"duration":1200,"easing":"cubic-bezier(0.2, 0.6, 0.0, 1)","fill":"both"}] [{"selector":"#anim-4b1ee993-6bcf-4231-8655-2bd6fcd52599","keyframes":{"transform":["translate3d(0px, -179.52069%, 0)","translate3d(0px, 0px, 0)"]},"delay":0,"duration":1200,"easing":"cubic-bezier(0.2, 0.6, 0.0, 1)","fill":"both"}] LEFT JOIN returns all rows from left table; RIGHT JOIN from right; FULL JOIN from both.

How INNER JOIN works

[{"selector":"#anim-01eee89c-be7e-468e-8282-9ee72d8591ca","keyframes":{"opacity":[0,1]},"delay":200,"duration":800,"easing":"cubic-bezier(0.2, 0.6, 0.0, 1)","fill":"both"}] [{"selector":"#anim-a7fa45c1-17c7-4d61-890f-e28092a69d28","keyframes":{"transform":["translate3d(0px, 325.73685%, 0)","translate3d(0px, 0px, 0)"]},"delay":200,"duration":800,"easing":"cubic-bezier(0.2, 0.6, 0.0, 1)","fill":"both"}] [{"selector":"#anim-8c3a0606-47d1-4359-b5fd-b4c65b58a3a7","keyframes":{"opacity":[0,1]},"delay":300,"duration":1200,"easing":"cubic-bezier(0.2, 0.6, 0.0, 1)","fill":"both"}] [{"selector":"#anim-c5b80d07-e149-4087-ad1f-bd40b48820b7","keyframes":{"transform":["translate3d(0px, 381.4178%, 0)","translate3d(0px, 0px, 0)"]},"delay":300,"duration":1200,"easing":"cubic-bezier(0.2, 0.6, 0.0, 1)","fill":"both"}] [{"selector":"#anim-91390256-40f0-4778-a841-d99585d25059","keyframes":{"opacity":[0,1]},"delay":300,"duration":1200,"easing":"cubic-bezier(0.2, 0.6, 0.0, 1)","fill":"both"}] [{"selector":"#anim-43cd1353-6994-4bbc-b966-bf7e1013d1ee","keyframes":{"transform":["translate3d(0px, 367.62467%, 0)","translate3d(0px, 0px, 0)"]},"delay":300,"duration":1200,"easing":"cubic-bezier(0.2, 0.6, 0.0, 1)","fill":"both"}] [{"selector":"#anim-ab52cd28-acb0-41ed-9f90-21c224bafe9e","keyframes":{"opacity":[0,1]},"delay":0,"duration":1200,"easing":"cubic-bezier(0.2, 0.6, 0.0, 1)","fill":"both"}] [{"selector":"#anim-520d77ab-dd0f-47a5-a4e6-75e556a0d166","keyframes":{"transform":["translate3d(0px, -162.08891%, 0)","translate3d(0px, 0px, 0)"]},"delay":0,"duration":1200,"easing":"cubic-bezier(0.2, 0.6, 0.0, 1)","fill":"both"}] Combines rows where columns match in both tables, excluding unmatched rows. Combines rows where columns match in both tables, excluding unmatched rows.

How OUTER JOIN works

[{"selector":"#anim-40cdeef7-eabb-4f93-96a1-aec7f5c4fce9","keyframes":{"opacity":[0,1]},"delay":200,"duration":800,"easing":"cubic-bezier(0.2, 0.6, 0.0, 1)","fill":"both"}] [{"selector":"#anim-ddd92ff0-9cf5-413b-97d6-32b81369a8b9","keyframes":{"transform":["translate3d(0px, 272.67564%, 0)","translate3d(0px, 0px, 0)"]},"delay":200,"duration":800,"easing":"cubic-bezier(0.2, 0.6, 0.0, 1)","fill":"both"}] [{"selector":"#anim-21edf6c2-3d2c-4de7-b105-a05455a6a1af","keyframes":{"opacity":[0,1]},"delay":300,"duration":1200,"easing":"cubic-bezier(0.2, 0.6, 0.0, 1)","fill":"both"}] [{"selector":"#anim-c042b57e-c517-40b0-9515-64a14cd859d5","keyframes":{"transform":["translate3d(0px, 279.33722%, 0)","translate3d(0px, 0px, 0)"]},"delay":300,"duration":1200,"easing":"cubic-bezier(0.2, 0.6, 0.0, 1)","fill":"both"}] [{"selector":"#anim-f0ab374f-1dd4-4fa1-919c-f8cdcbaf519e","keyframes":{"opacity":[0,1]},"delay":0,"duration":1200,"easing":"cubic-bezier(0.2, 0.6, 0.0, 1)","fill":"both"}] [{"selector":"#anim-13a39745-6ae9-4a03-b98c-b5a9c2187b8c","keyframes":{"transform":["translate3d(0px, -139.51751%, 0)","translate3d(0px, 0px, 0)"]},"delay":0,"duration":1200,"easing":"cubic-bezier(0.2, 0.6, 0.0, 1)","fill":"both"}] Includes all rows from one or both tables, filling unmatched sides with NULL values.

Use INNER JOIN when

[{"selector":"#anim-f862c2a9-3ee0-42e3-a529-b8533b270d02","keyframes":{"opacity":[0,1]},"delay":200,"duration":800,"easing":"cubic-bezier(0.2, 0.6, 0.0, 1)","fill":"both"}] [{"selector":"#anim-476391e4-9117-441f-a489-bf67d4814d1d","keyframes":{"transform":["translate3d(0px, 272.67564%, 0)","translate3d(0px, 0px, 0)"]},"delay":200,"duration":800,"easing":"cubic-bezier(0.2, 0.6, 0.0, 1)","fill":"both"}] [{"selector":"#anim-a44605a0-32a7-4525-88f5-0ed9c2c51ea8","keyframes":{"opacity":[0,1]},"delay":300,"duration":1200,"easing":"cubic-bezier(0.2, 0.6, 0.0, 1)","fill":"both"}] [{"selector":"#anim-05f0eb80-0b88-4252-a963-a6e342641c7f","keyframes":{"transform":["translate3d(0px, 274.52113%, 0)","translate3d(0px, 0px, 0)"]},"delay":300,"duration":1200,"easing":"cubic-bezier(0.2, 0.6, 0.0, 1)","fill":"both"}] [{"selector":"#anim-2c1605e7-b5e1-4835-9f48-388080c28067","keyframes":{"opacity":[0,1]},"delay":0,"duration":1200,"easing":"cubic-bezier(0.2, 0.6, 0.0, 1)","fill":"both"}] [{"selector":"#anim-be9f89aa-518c-4475-91ee-5febdbe62a31","keyframes":{"transform":["translate3d(0px, -208.5163%, 0)","translate3d(0px, 0px, 0)"]},"delay":0,"duration":1200,"easing":"cubic-bezier(0.2, 0.6, 0.0, 1)","fill":"both"}] You need only related data from both tables, focusing on intersection of data.

Use OUTER JOIN when

[{"selector":"#anim-f1fdbb01-4547-4aeb-a2ad-8eb336b053e7","keyframes":{"opacity":[0,1]},"delay":200,"duration":800,"easing":"cubic-bezier(0.2, 0.6, 0.0, 1)","fill":"both"}] [{"selector":"#anim-5b9ed91e-87a5-4f31-85cf-baccbce9c1d8","keyframes":{"transform":["translate3d(0px, 276.99211%, 0)","translate3d(0px, 0px, 0)"]},"delay":200,"duration":800,"easing":"cubic-bezier(0.2, 0.6, 0.0, 1)","fill":"both"}] [{"selector":"#anim-863166eb-6d44-4b13-8b2d-d4d9a7a20f72","keyframes":{"opacity":[0,1]},"delay":300,"duration":1200,"easing":"cubic-bezier(0.2, 0.6, 0.0, 1)","fill":"both"}] [{"selector":"#anim-70d86ce2-5967-4571-92b5-ba64baf528aa","keyframes":{"transform":["translate3d(0px, 284.86603%, 0)","translate3d(0px, 0px, 0)"]},"delay":300,"duration":1200,"easing":"cubic-bezier(0.2, 0.6, 0.0, 1)","fill":"both"}] [{"selector":"#anim-1efb03c3-fd96-432f-9454-115029c9b330","keyframes":{"opacity":[0,1]},"delay":0,"duration":1200,"easing":"cubic-bezier(0.2, 0.6, 0.0, 1)","fill":"both"}] [{"selector":"#anim-0e8d49d0-4fe9-415f-8027-a81150da3460","keyframes":{"transform":["translate3d(0px, -185.89324%, 0)","translate3d(0px, 0px, 0)"]},"delay":0,"duration":1200,"easing":"cubic-bezier(0.2, 0.6, 0.0, 1)","fill":"both"}] You want complete data, including unmatched records with nulls for missing matches.

Performance Note

[{"selector":"#anim-8c5c6b9e-8e55-4fba-be97-ed16f94f6bac","keyframes":{"opacity":[0,1]},"delay":200,"duration":800,"easing":"cubic-bezier(0.2, 0.6, 0.0, 1)","fill":"both"}] [{"selector":"#anim-ee277a73-6672-42a2-ae5c-de5ac3f5c3da","keyframes":{"transform":["translate3d(0px, 306.34913%, 0)","translate3d(0px, 0px, 0)"]},"delay":200,"duration":800,"easing":"cubic-bezier(0.2, 0.6, 0.0, 1)","fill":"both"}] [{"selector":"#anim-06883f6c-eec9-4493-b8f0-b54ad91a3f61","keyframes":{"opacity":[0,1]},"delay":300,"duration":1200,"easing":"cubic-bezier(0.2, 0.6, 0.0, 1)","fill":"both"}] [{"selector":"#anim-002b4ce3-235c-4fff-b89a-2e7a9ca79cbe","keyframes":{"transform":["translate3d(0px, 312.67063%, 0)","translate3d(0px, 0px, 0)"]},"delay":300,"duration":1200,"easing":"cubic-bezier(0.2, 0.6, 0.0, 1)","fill":"both"}] [{"selector":"#anim-b03927f5-a9d2-4e9e-8651-b988741a246a","keyframes":{"opacity":[0,1]},"delay":0,"duration":1200,"easing":"cubic-bezier(0.2, 0.6, 0.0, 1)","fill":"both"}] [{"selector":"#anim-cd600a9a-6190-441c-ae65-c8b8561ca999","keyframes":{"transform":["translate3d(0px, -208.96254%, 0)","translate3d(0px, 0px, 0)"]},"delay":0,"duration":1200,"easing":"cubic-bezier(0.2, 0.6, 0.0, 1)","fill":"both"}] INNER JOINs are usually faster since they retrieve only matching data, unlike OUTER JOINs .