What is the combination of table 1 and table 2

Question

SELECT *
FROM   table1,
       table2;

I would like to know what output it will give? Also is it called to be JOINS?

Maybe [this visual representation](http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html) could help you. — Guillaume Poussel, Oct 20 '13 at 14:00
@GuillaumePoussel - How would that help? It doesn't even show cross joins / cartesian joins. It is difficult to know how it could given that the Venn Diagram that is completely filled in is supposed to represent the full outer join. A better visual representation [might be this](http://stackoverflow.com/questions/13997365/sql-joins-as-venn-diagram/14011879#14011879) — Martin Smith, Oct 20 '13 at 17:04

score 2 · Answer 1 · answered Oct 19 '13 at 18:30

2

This is caled a CARTESIAN JOIN. It will combine every row in table1 with every row in table2.

It is nearly always a result of an error in writing the query. Joining unrelated data is an exremely rare operation.

answered Oct 19 '13 at 18:30

Walter Mitty

18,205
2
28
58

score 2 · Answer 2 · answered Oct 20 '13 at 16:56

This would give you every combination of the columns from a row in table 1 and the columns in table 2.

Let us say we have a departments table and an employees table and an employee can store the id of the department they belong to.

EMPLOYEES
ID  FORENAME    SURNAME DEPARTMENT_ID
1   A           X       1
2   B           Y       1
3   C           Z       2

DEPARTMENTS
ID  NAME
1   Department 1
2   Department 2

If we ran your query, it would do a cartesian:

ID  NAME         FORENAME   SURNAME DEPARTMENT_ID
1   Department 1 A          X       1
1   Department 1 B          Y       1
1   Department 1 C          Z       2
2   Department 2 A          X       1
2   Department 2 B          Y       1
2   Department 2 C          Z       2

For each department row we get the product with each employee. We get 3x2 rows.

If we change the query to "join" the tables based on a condition, in this case the department_id stored on an employee:

SELECT *
FROM departments d
JOIN employees e ON d.id = e.department_id

ID  NAME         FORENAME   SURNAME DEPARTMENT_ID
1   Department 1 A          X       1
1   Department 1 B          Y       1
2   Department 2 C          Z       2

We now have 3 rows as it is likely we intended. For department 1 we get the department 1 columns and the columns for the 2 employees that match/JOIN the department. Then we get the columns for department 2 and the columns from the employee row that can be JOINed.

If a department has no matching employees the department won't show up at all. This is where OUTER JOINs are used, where one side of the join will be returned even if there isn't any matching rows on the other side of the JOIN

What is the combination of table 1 and table 2

2 Answers2