Menu
I have 2 tables:
book ( id, title, age ) ----> 100 milions of rows
author ( id, book_id, name, born ) ----> 10 millions of rows
Now, supposing I have a generic id of a book. I need to print this page:
Title: mybook
authors: Tom, Graham, Luis, Clarke, George
So... what is the best way to do this ?
1) Simple join like this:
Select book.title, author.name
From book, author
WHERE ( author.book_id = book.id ) AND ( book.id = 342 )
2) For avoid the join, I could make 2 simple query:
Select title FROM book WHERE id = 342
Select name FROM author WHERE book_id = 342
What is the most efficient way ?
825
How to Use Join Query in SQL with Examples. 1. Left Join. Left Join = All rows from left table + INNER Join. Let us consider two tables and apply Left join on the tables: –. 2. RIGHT Join. 3. INNER Join. 4. FULL OUTER Join.
To join more than one table we need at least one column common in both tables. Tables get joined based on the condition specified. This is a guide to Join Query in SQL. Here we also discuss the introduction and different types of joins along with different examples and its code implementation.
Right Join gets all the rows from the Right table and common rows of the both table. Let us take an example of the right join. Below represents the Venn diagram of the right join. In below diagram Table A is right join to the table B. Here all the rows from the table B gets considered and common rows from both table. 4. FULL Join
For inner joins, a single query makes sense, since you only get matching rows. For left joins, multiple queries is much better... look at the following benchmark I did: Single query with 5 Joins query: 8.074508 seconds
The first one. It's only a single round trip. It requires a little processing to collapse the rows of authors into a comma-separated list like you want but that's basically boilerplate code.
Separate related queries are a bad habit that will kill your performance faster than most things.
132
The best option is to run speed tests on your own server. Depending on how often the different tables are accessed together and apart, either one could be faster.
This has been answered in depth before: LEFT JOIN vs. multiple SELECT statements
40
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
