Working with Joins and Subqueries

1. Working with Joins and Subqueries

So far, we've only queried data from a single table. But in real applications, data is rarely in just one place.

2. Data lives in multiple tables

Think about our library database. Here's a diagram showing some of the tables and their relationships. Books live in one table, authors in another, and the book_authors table links them together. We also have categories and book_reviews connected to books. To answer real-world questions, we need to combine data across tables. That's where JOINs and subqueries come in.

3. Joining Tables

Let's say we want to display books along with their authors. The data lives in three different tables, so we need to JOIN them together. We start by selecting book titles and author names. Then we use INNER JOIN twice: first connecting books to the book_authors table, then linking that to authors. Finally, we add a WHERE clause to filter books published after 2010. Here is the snippet of the first few rows we get. Notice how powerful this is: instead of running multiple queries and merging results in Java, the database does the heavy lifting and sends us exactly the joined data we need.

4. INNER vs LEFT JOIN

Keep in mind, there are different types of JOINs we can use. INNER JOIN returns only rows where both sides match. If we joined books and reviews tables, we'd only get books that have reviews. LEFT JOIN returns all rows from the left table, even without a match on the right. For books without reviews, we'd get NULL ratings, like for Book B in our example.

5. Subqueries

Sometimes, we need data that depends on another query. That's where subqueries come in. Here, we want the most recently published book. Instead of fetching all books and sorting them in Java, we let the database handle it. The inner query uses MAX to find the latest publication year, and the outer query selects books from that year. If we print the results, we get the following recently published books. This pattern keeps queries efficient and shifts complexity to SQL, where it belongs.

6. Subqueries with Parameters

What's interesting is that subqueries can also use parameters. Here, we want all books in the Fantasy category. Instead of hardcoding the category ID, we query the categories table inside a subquery, binding the category name safely with the setString method. Our query returns the following two books.

7. StringBuilder

As our SQL queries become more complex, string concatenation using the plus operator becomes slow and difficult to read. That's where StringBuilder comes in. We create one using a new StringBuilder and pass an initial string. Then we call the append method to add more text, and we can chain multiple appends together. When we're done building, we call toString to get the final string. This approach is much cleaner for dynamic queries where we conditionally add clauses like WHERE or ORDER BY.

8. Let's practice!

Let's write some JOINs and subqueries in Java!

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.