Inner Join, And, Or, Having, and Between SQL Clauses (part 5 of 8)Learn about some of the more advanced SQL statements.
In the previous article, What are the SQL Order By Clause and Expression statement language elements?, we learned about the structure of SQL defining the clauses, expressions defining the overall statement. Also, we used the Select statement to retrieve the last names in descending order, using the Order By clause. In this article, you will learn about some of the more advanced SQL statements and clause commands such as Inner Join, AND, OR, Having, and Between. We will also learn more about the SQL syntax or rules of the SQL language.
Remember, our sample database used in this series of articles was named realparsmodel.
Also, With the Entity Relationship Diagram, you can see the realparsmodel database has several tables that are related to one another.
Here is our last SQL statement that we have written in the SQL Query Tab, using the Order By clause.
In general, the Order By clause will be used along with the Select statement to provide a descending or ascending listing of the results provided by the query.
The AND operator is a logical operator that combines two or more Boolean expressions and returns true only if both expressions evaluate to true.
Now using the SELECT statement from the Students table where the results display only records having the country of USA AND the state of California AND having a Credit limit greater than $100,000, the query would be written like this:
Press the Execute Query button to run the statement. By doing this, it shows a student from California with a large credit as a result.
The OR operator is a logical operator that combines two or more Boolean expressions and returns true when either condition is true.
Now using the SELECT statement from the Students table, where the results display records having the country of USA OR the country of France, the query would be written like this:
Once again, Press the Execute Query button to run the statement. By doing this, it shows a list of the students from the USA or France as a result.
The BETWEEN operator is a logical operator that allows you to specify whether a value is in a range or not.
Now using the SELECT statement from the Courses table where the results display records having a price BETWEEN 90 and 100, the query would be written like this:
To run this SQL statement, Press the Execute Query button. By doing this, it shows the records of the price range between 90 and 100 as a result.
INNER JOIN Clause
The INNER JOIN clause matches rows in one table with rows in other tables and allows you to query rows that contain columns from both tables.
For example, you can join tables that have key relationships like the Courselines and Courses tables.
We want to get the Coursecode and Coursename from the Courses table and the TextDescription of course lines from the Courselines table.
To do this, write the SQL statement to select data from both tables by matching rows based on the Course line columns, using the INNER JOIN clause as follows:
By pressing the Execute Query button, it shows the records of Course code, Course name and text description columns.
The HAVING clause is used in the SELECT statement to specify filter conditions for a group of rows or aggregates.
Now the department asks us to write the SQL statement, using the HAVING clause from the Order details table, with results displaying the Order number column data with two calculated columns for the quantity and total amount paid for each order only having totals greater than 1000 and quantity greater than 600.
This concludes the article, What are the SQL Inner Join, And, Or, Having and Between Clause statement language elements.
Next SQL Lessons
Our series of subsequent articles to follow will consist of the following lessons.
Be sure to read these articles offering prerequisite learning for the beginner and then unto the more advanced statements of SQL learning.
The next SQL lessons soon to be available are:
– Subquery and Exists Clause statement language elements
– Create Table Clause statement language element
– Cross Join, Inner Join, and Union Clause statement language elements
Want to Learn More?
By downloading the RealPars app, you can have access to a wealth of practical knowledge as an automation engineer right in your pocket and you will also receive new fresh out of the oven videos each and every week.
If you would like to get additional training on a similar subject please let us know in the comment section.
Check back with us soon for more automation control topics.
Got a friend, client, or colleague who could use some of this information? Please share this article.
5 ACTIONABLE TIPS FOR GETTING A PLC PROGRAMMING JOB WITH NO EXPERIENCE The mindset that helped me find my ideal job as a PLC programmer with NO experience. Working as a PLC Programmer is one of the most attractive and highest paying jobs in the engineering industry....
With this article, we will continue with where our previous article, What are the leading Industrial Automation Job types?, closed with beginning the discussion on Automation Job Interview questions. So now that we have learned about the Automation Job types let's...
RealPars is the world's largest online learning platform for automation engineers.
+31 10 316 6400
Mon - Fri 8:30 am to 5:30 pm (CET)
Rotterdam Science Tower, Marconistraat 16,
3029AK Rotterdam, The Netherlands
© 2020 RealPras B.V. All rights reserved.
Created with coffee and tea in Rotterdam.