Joining tables in SQL requires a good understanding of the data, the relationships between the tables, and the behavior of the different join types. This GitHub repository covers some of the more advanced concepts of SQL joins and serves as a collection of interesting, odd, and uncommon ways you may see or think of joins in your everyday SQL encounters.
I've tried to keep all my examples as concise as possible, and they should serve as a springboard for further exploration. In this repository there are several markdown documents I have created that showcase different joins and concepts, and I try to show alternative ways in which you can write the SQL statement as a means of understanding their behavior. I have tried to create the documents in such a way they can be read in any order without trying to sound repetitive, but I do offer a recommended reading order that I provide at the end of each document.
Although I will talk about the logical processing order and physical join types, but any discussion about query optimization and best practices is at a cursory level. My intent here is not to show best practices or best optimization methods, but to provide a summarization of the different joins that you will encounter in your daily SQL activities and a sort of museum of odd and strange joins that I've encountered in my many years of SQL programming. To show the various joins, I use a small sample dataset that contains types of fruit that you can find in the Sample Data markdown file. The sample data has NULL markers in the sample data, but not duplicate data. Feel free to add, subtract and modify the data and these queries to explore their behavior.
I welcome any corrections, additions, debates etc. I've tried to show different joins across all the major database platforms, and I am sure there are some new and interesting joins that I have not included here (such as graph joins). Feel free to contact me through this GitHub repository or my Wordpress site at https://advancedsqlpuzzles.com.
⌨️ All SQL is written in Microsoft SQL Server T-SQL, unless otherwise noted.
- Introduction
- SQL Processing Order
- Table Types
- Equi, Theta, and Natural Joins
- Inner Joins
- Outer Joins
- Full Outer Joins
- Cross Joins
- Semi and Anti Joins
- Any, All, and Some
- Self Joins
- Relational Division
- Set Operations
- Join Algorithms
- Exists
- Complex Joins
📫 If you find any inaccuracies, misspellings, bugs, dead links, etc. please report an issue! No detail is too small, and I appreciate all the help.
😄 Happy coding!