_edited_edited.png)
A complex query in SQL is a query that contains multiple clauses, such as SELECT, FROM, WHERE, GROUP BY, ORDER BY, and HAVING. These clauses can be used to filter, sort, and aggregate data from multiple tables.
As the name suggests, a complex query has a complex syntax and can comprise multiple parts. A purpose of such a query is to search data based on several parameters.
How do I create a complex SQL query?
Open Query Builder. ...
Add tables to the query. ...
Create a subquery. ...
Create JOINs between the tables. ...
Build WHERE or HAVING clause. ...
Create GROUP BY or ORDER BY clause. ...
View and execute the query. ...
Analyze the result.
SQL Complex Queries: Functionality & Examples
Consider the huge amount of data that would be present in a music database like Spotify. Sometimes you want to search for more than just a single artist or album, or maybe you want to pull together different subsets of data. Let's first take a look at how this might work if you were a database administrator working with an inner join method.
Inner Join Let's say that you have a music database. Albums, artist, genres, reviews, etc. are all stored in separate tables. If you want to get all records from two tables that are related, you can use a simple inner join. This selects data where the values match in both tables. Check out these tables. The first one is some sample data and the second one is the albums table: Sample Data
artistID | artistName |
1 | Journey |
2 | Meat Loaf |
3 | Enya |
4 | Kate Wolf |
5 | Aerosmith |
Albums table
albumID | artistID | albumTitle |
1 | 1 | Raised on Radio |
2 | 1 | Greatest Hits |
3 | 2 | Bat out of Hell |
4 | 2 | Dead Ringer |
5 | 3 | The Celts |
6 | 4 | Poet's Heart |
This code is how the inner join would appear:
SELECT *
|
Now, with this new table we can see that the result is 6 rows because we only care about those records where the IDs match.
albumId | tblAlbum.artistID | albumTitle | tblArtist.artistID | artistName |
1 | 1 | Raised onRadio | 1 | Journey |
2 | 1 | Greatest Hits | 1 | Journey |
3 | 2 | Bat out of Hell | 2 | Meat Loaf |
4 | 2 | Dead Ringer | 2 | Meat Loaf |
5 | 3 | The Celts | 3 | Enya |
6 | 4 | Poet's Heart | 4 | Kate Wolf |
Now, here is a graphic representing an inner join. Looks pretty simple, right?
Outer Join
Now, let's take a closer look at using the outer join method. A left outer join returns ALL matching records in the left-hand table, plus the records that match in the middle (the inner join).
The SQL statement is as follows:
1. SELECT * FROM tblArtist 2. LEFT OUTER JOIN tblAlbum ON 3. tblArtist.artistID = tblAlbum.artistID; |
This generates the seven rows because now we get the record for Aerosmith. This band doesn't have any albums entered yet, since there are so many to add. However, when running the left outer join, we get to see their entry.
tblArtist.artistID | artistName | albumID | tblAlbum.artistID | albumTitle |
1 | Journey | 1 | 1 | Raised on Radio |
1 | Journey | 2 | 1 | Greatest Hits |
2 | Meatloaf | 3 | 2 | Bat out of Hell |
2 | Meatloaf | 4 | 2 | Dead Ringer |
3 | Enya | 5 | 3 | The Celts |
4 | Kate Wolf | 6 | 4 | Poet's Heart |
5 | Aerosmith | | | |
And now,here is a graphic that represents the left outer join:
Right Outer Join
Now, let's take a look at the right outer join method. Let's say that you had an album called Unknown, and it wasn't yet tied to an artist quite yet. To retrieve this data element, you can use the right outer join. This is like the left outer, except you retrieve matching records plus those in the right-hand table.
Here is the SQL statement:
1. SELECT * 2.FROM tblArtist RIGHT OUTER JOIN tblAlbum ON tblArtist.artistID = tblAlbum.artistID; |
Full Outer Join (Rows That Don't Join)
Now let's take a look a full outer join, in which we have rows that don't join. Consider that we've inserted some new albums and some new artists in the database, but we haven't joined them together. You can see this playing out in the code:
1.SELECT * FROM tblArtist 2.FULL OUTER JOIN tblAlbum ON 3.tblArtist.artistID = tblAlbum.artistID 4.WHERE 5.tblArtist.artistID = NULL or tblAlbum.artistID = NULL; |
This would result in something like this table:
albumID | tblAlbum.artistID | albumTitle | tblArtist.artistID | artistName |
5 | Aerosmith | | | |
Next lets see How to find Nth highest salary from a table
This is considered to be most frequently asked question in the interviews
Here is a way to do this task using dense_rank() function.
Consider the following table:
Employee
Employee Name | Employee Salary |
John | 31000 |
Mike | 24500 |
Brandon | 23000 |
Oliviya | 28500 |
Jessy | 31500 |
Mark | 29000 |
Gabbi | 51000 |
Richard | 41000 |
Mathew | 42000 |
Raj | 38000 |
Query :
select * from(
select ename, sal, dense_rank()
over(order by sal desc)r from Employee)
where r=&n;
To find to the 2nd highest sal set n = 2
To find 3rd highest sal set n = 3 and so on.
DENSE_RANK:
DENSE_RANK computes the rank of a row in an ordered group of rows and returns the rank as a NUMBER. The ranks are consecutive integers beginning with 1.
This function accepts arguments as any numeric data type and returns NUMBER.
As an analytic function, DENSE_RANK computes the rank of each row returned from a query with respect to the other rows, based on the values of the value_exprs in the order_by_clause.
In the above query the rank is returned based on sal of the employee table. In case of tie, it assigns equal rank to all the rows.
An Alternate Solution for doing this is
CREATE TABLE `Employee` (
`ENAME` varchar(225) COLLATE utf8_unicode_ci NOT NULL,
`SAL` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`ENAME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; 6th highest select * from ((select * from Employee ORDER BY `sal` DESC limit 6 ) AS T) ORDER BY T.`sal` ASC limit 1; Alternate use of Limit: select * from Employee ORDER BY `sal` DESC limit 5,1; // will return 6th highest
One More way to do this is
Suppose the task is to find the employee with the Nth highest salary from the above table. We can do this as follows:
Find the employees with top N distinct salaries.
Find the lowest salary among the salaries fetched by the above query, this will give us the Nth highest salary.
Find the details of the employee whose salary is the lowest salary fetched by the above query.
Query:
SELECT * FROM Employee WHERE sal = ( SELECT MIN(sal) FROM Employee WHERE sal IN ( SELECT DISTINCT TOP N sal FROM Employee ORDER BY sal DESC ))
The above query will fetch the details of the employee with the Nth highest salary. Let us see how:
Consider N = 4.
Starting with the most inner query, the query: “SELECT DISTINCT TOP 4 sal FROM Employee ORDER BY sal DESC” will produce the below result:
51000 42000 41000 38000
The next outer query is: “SELECT MIN(sal) FROM Employee WHERE sal IN ( Result_Set_of_Previous_Query )“. This will return the below result:
38000
You can see that the above returned result is the required 4th highest salary.
Next is the most outer query, which is: “SELECT * FROM Employee WHERE sal = Result_of_Previous_Query“. This query will return the details of employees with 4th highest salary.
Employee name | Employee salary |
Raj | 38000 |
Conclusion:
As mentioned before purpose of such complex queries is to search data based on several parameters.
SQL supports complex queries because it is a very expressive and mature language.
Hope this blog gave you a better idea in handling complex querries.I have reffered geeksforgeeks.com and study.com for this blog.
Comments