In the last lesson, we learned how it can sometimes make sense to run additional database queries when it allows us to run each individual query in the most performant way. We saw how doing so brought our overall query time from just under 100 milliseconds down to 3.93 milliseconds. In this lesson, we're going to look at how we can get the same benefits of running multiple queries, but in a single database query. You see, databases already have a way to run multiple queries in isolation within a single query. And you do that with unions. Let's jump into TablePlus and do some experimenting. Let's start by running a query that finds our users based on their first and last name.

Let's jump into TablePlus and do some experimenting. Let's start by running a query that finds our users based on their first and last name. SELECT * FROM users WHERE first_name LIKE ? OR last_name LIKE ?. And if we run this query, we can see that it's very fast. It runs in one millisecond. And if we run an EXPLAIN on it, we can see that it's using our first name and last name indexes. Now let's write a second query, this time finding users based on their company name. SELECT * FROM users, except this time we're going to INNER JOIN our companies table where company_id equals users.company_id. And of course, we'll do a check to make sure that our company_name is like our search term.

it's time to bring them together. We can do that by simply adding a UNION statement between them. However, we have to make one little change here. When you run a UNION, each query within that UNION must return the exact same number of columns. Right now, our company's query is returning both the user's columns and the company's columns. So let's just update that to be users.* to just get the user's columns. Now if we run the whole query, we can see that it's running very fast at one millisecond. Plus, if we run an EXPLAIN on this, we can see that it's using all of our indexes, the firstName, the lastName, and the companyName indexes. This is really what we've been trying to do this whole time.

the first name, the last name, and the company name indexes. This is really what we've been trying to do this whole time. But how exactly do we bring this to our User query in our app? Well, we can use another whereIn clause. Let's try that here in TablePlus first. Let's select * from users where the user_id is in. And now we're going to use our entire union query as a where in subquery. We'll just have to make one small change. A subquery can only return one column, but we're returning all the columns from our union query. Let's just update that. select id from users and select id from users.

A subquery can only return one column, but we're returning all the columns from our union query. Let's just update that. Select ID from users and select ID from users. Now let's run this query. Hmm. We're all the way back up to 450 milliseconds. What happened? Let's run an EXPLAIN to see if we can figure it out. Well, first off, we can see that our query is no longer using our first_name, last_name, or company_name indexes. So that's not good. If we look at our select type column here, we get a hint of what's wrong here. See how we have a dependent subquery and two dependent unions? That's not good. That means our new union query is not running in isolation. Rather, it's being considered a dependency of the parent query, and that's throwing everything off.

That means our new union query is not running in isolation. Rather, it's being considered a dependency of the parent query, and that's throwing everything off. In situations like this, I like to reach for a derived table. A derived table can break the dependency between the inner and outer queries, and it's actually really simple to implement. Within our where in subquery, we're just going to wrap it in another query where we select the ID from a subquery. And that subquery, again, is going to be our union. And we just need to add a table alias. A derived table is simply a FROM statement that has a subquery within it. Okay, let's rerun the EXPLAIN. Right away here, if we look at the select type column, we can see that we no longer have any dependencies. What's more, if we look at our indexes column,

Okay, let's rerun the explain. Right away here, if we look at the select type column, we can see that we no longer have any dependencies. What's more, if we look at our indexes column, we can see that we're now using our first name, last name, and company name indexes. Very, very cool. And if we remove the explain and run the query again, you can see that we're back down to one millisecond. And what's really cool about this approach is it works for more than one search term. Let me show you. Let's copy this entire where condition and just say, and paste that in and then change our second search term to be Microsoft. And if we run this query, we're now seeing our single result for Bill Gates and it's still running within one millisecond.

And if we run this query, we're now seeing our single result for Bill Gates and it's still running within one millisecond. I don't know about you, but I think that's pretty awesome. Okay, let's bring this all back to our app. So how exactly are we going to update our search scope to run this new query we've built? Let's think about the steps that we need to take. We first need to do a whereIn to find all the users that have a match. Then we need our derived table to break any dependencies between our inner and outer queries. Then we need to find the users based on their first and last names. Then we need our union. And finally, we need to find our users by their company name.

Then we need to find the users based on their first and last names. Then we need our union. And finally, we need to find our users by their company name. Let's implement this. Query where in ID and then let's pass in a closure for our sub query and that'll take an instance of the query builder. And as always, we need to pass through our searchTerm. All right, step one done. Now we need to introduce our derived table. Query select ID from. And now instead of putting a table name here, we need to write a sub query. Again, we'll use a closure here that takes the query builder and will pass through our searchTerm.

And now instead of putting a table name here, we need to write a subquery. Again, we'll use a closure here that takes the query builder and will pass through our searchTerm. The only thing we need to remember to do is to give this derived table an alias name. Step two done. Now we need to write our query to find the users based on their firstName and lastName. Query select id from users where firstName like searchTerm or lastName like searchTerm. Okay, we're getting there. We've now got the third step completed. Now's where it gets a little bit interesting. Now we need to introduce the union.

Now's where it gets a little bit interesting. Now we need to introduce the union. Laravel actually makes this quite easy to do. To add a union, you simply call the union method passing through a second query builder instance. However, where are we going to get that query builder instance? One approach is to use the database facade. However, instead of having to import that class, I prefer to just generate a new query from our existing query. All right, so now we have our union in place. The only thing we have left to do is to find our users based on their company name.

All right, so now we have our union in place. The only thing we have left to do is to find our users based on their company name. We're going to select users.id from the users table. And we're going to join in the companies table where the company_id equals our users.company_id. And finally, we'll just add the where condition to check that the company_name is like our search term. We've now completed all of our steps. Now let's go to the browser and make sure this actually works. As a refresher, we're currently at 6 database queries and that's running at 3.93 milliseconds. Let's hit refresh and we're now at 3.84 milliseconds. Beautiful. We've successfully reworked our query to get the benefits of running separate queries,

Let's hit refresh and we're now at 3.84 milliseconds. Beautiful. We've successfully reworked our query to get the benefits of running separate queries, but as a single query. If we look here, we can see that we're now only running three database queries instead of the six that we were previously running. In the next lesson, we'll switch gears a little and look at how we can order these User records in the most efficient way.