top of page
hand-businesswoman-touching-hand-artificial-intelligence-meaning-technology-connection-go-

Complex Queries in SQL

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. ...

  1. Add tables to the query. ...

  2. Create a subquery. ...

  3. Create JOINs between the tables. ...

  4. Build WHERE or HAVING clause. ...

  5. Create GROUP BY or ORDER BY clause. ...

  6. View and execute the query. ...

  7. 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 *

  1. FROM tblAlbum

  2. INNER JOIN tblArtist ON

  3. tblAlbum.artistID = tblArtist.artistID;


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:


  1. 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.

  2. This function accepts arguments as any numeric data type and returns NUMBER.

  3. 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.

  4. 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:

  1. Find the employees with top N distinct salaries.

  2. Find the lowest salary among the salaries fetched by the above query, this will give us the Nth highest salary.

  3. 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.

29 views0 comments
bottom of page