In this guide, we're going to extend our knowledge of the inner join. And we're going to start to build in some more complex query logic into our results. So I'm going to start by walking through how we can leverage a WHERE clause inside of a join statement. I am going to say select all from guides g.

I'm aliasing in the Guides table, and I'm going to say join users u, and I'm going say on g guides users ID and map that to you u.users id. Now let's say where u.users we could do email, or we could do name. So I mean a name is equal to Tiffany.

Now if I run this query this is going to bring up all of the guides but only the guides that where the user's name is Tiffany.

You can see this brings up three guides which is exactly what we want. It completely ignores any guides that were not written by Tiffany. So this is a way where we can implement that kind of logic.

Now let's extend this out a little bit just so you can see that join tables are just like any other SQL query, you can perform any of the types of functions and conditional logic that you would normally. So here I'm going to have the same first set of items except I'm going to get rid of this right here and I'm going to say where g guides revenue is greater than 700.

You can perform the same type of query where you say I want you to bring me back all of the guides and their respective users for each guide that is has made over $700. So now if I run this.

You can see that this has now returned a smaller resultset and it contains all of the guys that's where the revenue is greater than 700. But we're not limited to only giving one conditional I can say, and u.user's name is equal to Tiffany.

If I run this now, it limits the results again, and we can also put in an or clause. Some I'm going to say, or u.user's name is equal to Kristine. Now if I run this you can see we're back to pretty much where we started right here because we only have two users.

Now if I run this.

But in a normal scenario, this would allow you to have very targeted types of queries. That's really what I wanted to reiterate here, is that a lot of times people when they are learning SQL, the concept of a join table may make sense in regards to what they want to do. But the syntax and some of the approaching it may seem a little bit intimidating.

I just wanted to kind of reinforce the fact that this is just like a normal query. This is just like we did before when we said select all from Guides where the guides revenue was over 700. The difference is now that we have joined we just have different data points that we can add so we can now run
criteria for a user's name and a user's email.

The most logical way of doing it or at least the way that helped me when I was learning SQL. Is having an understanding that all doing is really doing in regards to building the query is it's giving me access to more data. It's giving me access to run more specific types of queries and access to different column names.

Then it started to become a little bit less intimidating. So this is how you can perform more complex queries using inner joins.

Code

USE devcamp_sql_course_schema;

SELECT *
FROM guides g
JOIN users u
ON g.guides_users_id = u.users_id
WHERE u.users_name = 'Tiffany';

SELECT *
FROM guides g
JOIN users u
ON g.guides_users_id = u.users_id
WHERE g.guides_revenue > 700
AND u.users_name = 'Tiffany'
OR u.users_name = 'Kristine';