Quick note on CROSS JOIN

Quick note on CROSS JOIN

December 9, 2007 in Development, SQL
Written by: Dag Jonny Nedrelid

This is one of those things that’s nice to know when starting out as a web developer. Instead of writing 2 queries to first get 1 value from 1 table, just to be able to use it in another query against the same table again, you can instead use a subquery as a table in your main query.




Example: If you want to retrieve a last row of information from a table, you could search for the max/highest row ID that has been created in that table to be sure that you are working against the latest one.

"SELECT tblOne.* FROM tblOne, (SELECT MAX(ID) as maxID FROM tblOne) maxresults WHERE tblOne.ID = maxresults.maxID"

It does not need to be the same table in the subquery. You can compare it against using a “table view” stored in the DBMS, it’s a good middle thing to use when you think the query is not quite big enough to be wanting to create a view for it.

It is not that often this method is needed, but there are times. When you need 2 subqueries in 1 query it’s called a CROSS JOIN.

Leave a Reply





Welcome to Thronic.com

My name is Dag Jonny Nedrelid. I'm an IT consultant and computer programmer who lives in Southern Norway. Computers have been one of my hobbies and line of work since 1995.

If you want to contact me, you can send me an e-mail. My e-mail address