CODEWITHSUNDEEP
sqlsqlite
RNA
RNA

Reputation: 153261

link table for multiple-to-multiple relationship

Let's say I want to store two tables, book and library, in a city.

CREATE TABLE IF NOT EXISTS book (
       isbn  TEXT   PRIMARY KEY,
       book_title       TEXT   NOT NULL,
       id  INT     NOT NULL);

CREATE TABLE IF NOT EXISTS library (
       library_name  TEXT   PRIMARY KEY,
       location       TEXT   NOT NULL,
       id  INT     NOT NULL)

The relationship between the 2 tables is multiple-to-multiple. Sometimes I want to get the libraries that have a specific book and sometimes I want to get the books that a library has. To do that, I should have a link table like the following, right?

CREATE TABLE IF NOT EXISTS book_library (
       book_id INT FOREIGN KEY REFERENCES book(id),
       library_id INT  FOREIGN KEY REFERENCES library(id));

And to get the libraries have book with ISBN of 978-3-16-148410-0, should I do following?

SELECT libary_name FROM library WHERE id in (SELECT library_id FROM book_library WHERE book_id in (SELECT id FROM book WHERE isbn = "978-3-16-148410-0"));

And to get the book titles of the library Gorgeous Library:

SELECT bool_title FROM book WHERE id in (SELECT book_id FROM book_library WHERE library_id in (SELECT id FROM library WHERE library_name = "Gorgeous Library"));

I am wondering if this is the correct way and most efficient?

Upvotes: 0

Views: 64

Answers (2)

CL.
CL.

Reputation: 180020

The schema and the queries look correct. (But if you know that a subquery will return exactly one result, you can use = instead of IN.)

SQLite always implements joins as nested loop joins, so the performance of queries with joins is usually similar to equivalent queries that use subqueries. You should write queries in the most obvious and maintainable way; optimize them only if you have an actual problem.

In general, creating indexes on columns that are used for lookups is likely to make queries faster (for any kind of query). To check whether indexes are actually used in a specific query, use EXPLAIN QUERY PLAN.

The only way to find out which query is faster is to measure them.

Upvotes: 1

vibhuti
vibhuti

Reputation: 11

Here, primary key is isbn in book and name in library, and search is based on Id which is bit confusing.

if you want ISBN and library name to be unique, you can use UNIQUE Constraint.

however you can try JOIN instead of subquery. choosing one of the method is subjective - i have gone thru this blog post IN vs JOIN by Quassnoi

For this query, join is :

 Select Distinct L.library_name  from Library L
 join Book_library BL ON L.Id= BL.Library_ID
 join Book B on B.Id= BL.Book_Id
 where B.ISBN = "978-3-16-148410-0"

Upvotes: 1

Related Questions