Colatting data from multiple tables [closed]

Closed. This question needs details or clarity. It is not currently accepting answers.

---

Want to improve this question? Add d开发者_如何学Pythonetails and clarify the problem by editing this post.

Closed 9 years ago.

Improve this question

When I am retrieving data from multiple tables in my mySQL database what approach is best to use, joins, unions or view? What are the advantages and disadvantages of each and what circumstances do they suit best?

---

That really depends on how you want this data to be displayed.

Joins are used for when you want to select data that is related and display it all in the same row, where Unions are used for appeneding data from two different select statements together.

In your case, a view should be used when you have a very complex query, and you want to simplify the select statement that is needed to select the data. It will make it less confusing if you are going to use the select statement over and over. There are plenty of other reasons to use views, but I do not think they are relavent to this question.

---

What @jworrin said.

SQL is all about set operations.

• A inner join B is set intersection: A ∩ B

• A union B is ... well ... set union: A ∪ B

• A left join B is a composite: ( A ∉ ( A ∩ B ) ) ∪ ( A ∩ B )

• right join is the same as a left join. Just syntactic sugar to swap positions of the tables/sets involved. B right join A is identical to A left join B.

• A full join B is also a composite: ( A ∉ ( A ∩ B ) ) ∪ ( A ∩ B ) ∪ ( B ∉ ( A ∩ B ) )

Which you use is entirely dependent on the problem.

A view is essentially an invariant, canned select statement. It lets you present "virtual tables", for instance, a de-normalized, flat view of a normalized database. Views are also useful for such things as restricting access to columns and/or tables.