Need help understanding JOINS in SQL

Question

In an interview, I was asked a question below SQL. Please explain how it works and what it means.

Q: There are two tables: the emp table contains 10 rows, and the table contains 12 rows.

Select * from emp,department; 

What is the result and what is this connection?

Answer 1

It will return the Cartesian product of the two tables, which means that every combination of emp and department will be included in the result.

I believe the following question: Blockquote

How do you show the right department for each employee?

That is, show only the combination of emp and department , where the employee belongs to the department.

It can be done:

 SELECT * FROM emp LEFT JOIN department ON emp.department_id=department.id; 

Assuming emp has a field called department_id and department has a corresponding id field (this is pretty standard on these types of questions).

LEFT JOIN means that all elements on the left side ( emp ) will be included, and each employee will be coordinated with the corresponding department. If the corresponding department is not found, the received fields from departments will remain empty. Note that exactly 10 rows will be returned.

To show only employees with valid department IDs, use JOIN instead of LEFT JOIN . This query will return from 0 to 10 rows, depending on the number of identifiers of the compliance department.

Answer 2

The connection you specify is a cross join . It will create one row for each combination of records in the joined tables.

I will give you the math from there.

Answer 3

This will result in a cross join, I believe, returning 120 rows. One row for each pair of row combinations from each of the two tables.

All in all, a pretty useless mix in most cases.

Answer 4

You will get all rows from both tables, each of which will be combined.

This is called a Cartesian compound and is very bad.

You will receive a total of 120 lines.

Answer 5

This is also the old implied syntax (18 yeasr is deprecated) and random cross joins are a common problem with this syntax. It should never be used. Explicit compounds are the best choice. I would also mention this in an interview and explain why. I would also not do this work if I actually used the crappy syntax, because it shows very well that the database is likely to be poorly designed.