Visual Representation of SQL Joins. Introduction This is just a simple article visually explaining SQL JOINs.
Background I'm a pretty visual person. Things seem to make more sense as a picture. I looked all over the Internet for a good graphical representation of SQL JOINs, but I couldn't find any to my liking. Using the code I am going to discuss seven different ways you can return data from two relational tables. For the sake of this article, I'll refer to 5, 6, and 7 as LEFT EXCLUDING JOIN, RIGHT EXCLUDING JOIN, and OUTER EXCLUDING JOIN, respectively. Inner JOIN This is the simplest, most understood Join and is the most common. Hide Copy Code SELECT <select_list> FROM Table_A A INNER JOIN Table_B B ON A.Key = B.Key Left JOIN This query will return all of the records in the left table (table A) regardless if any of those records have a match in the right table (table B). SELECT <select_list>FROM Table_A A LEFT JOIN Table_B B ON A.Key = B.Key.
A Visual Explanation of SQL Joins. I love the concept, though, so let's see if we can make it work.
Assume we have the following two tables. Table A is on the left, and Table B is on the right. We'll populate them with four records each. id name id name -- ---- -- ---- 1 Pirate 1 Rutabaga 2 Monkey 2 Pirate 3 Ninja 3 Darth Vader 4 Spaghetti 4 Ninja Let's join these tables by the name field in a few different ways and see if we can get a conceptual match to those nifty Venn diagrams. There's also a cartesian product or cross join, which as far as I can tell, can't be expressed as a Venn diagram: SELECT * FROM TableA CROSS JOIN TableB This joins "everything to everything", resulting in 4 x 4 = 16 rows, far more than we had in the original sets. Join (SQL) A programmer writes a JOIN statement to identify the records for joining.
If the evaluated predicate is true, the combined record is then produced in the expected format, a record set or a temporary table. Relational databases are often normalized to eliminate duplication of information when objects may have one-to-many relationships. For example, a Department may be associated with many different Employees. Joining two tables effectively creates another table which combines information from both tables. This is at some expense in terms of the time it takes to compute the join. Note: In the Employee table above, the employee "John" has not been assigned to any department yet. This is the SQL to create the aforementioned tables. CROSS JOIN returns the Cartesian product of rows from tables in the join. Example of an explicit cross join: SELECT *FROM employee CROSS JOIN department; Example of an implicit cross join: SELECT *FROM employee, department; We can write equi-join as below,