How to join two tables in a single SQL query? MySQL Example

Hello guys, if you are wondering how to join multiple tables in SQL to produce a combine result which contains columns from all tables but not sure how to do it then you have come to the right place. SQL Join is one of the basic concepts while working in databases or tables but yet less understood and most confusing topic for beginners and even intermediate developers. I compare Joins in SQL  with Recursion in programming in terms of confusion because I have found that these two topics are special in their nature and you can't get it right with casual reading until you understand the concept and its various well.

How to Join Two Unrelated Tables in SQL using Cross Join? Example

 In one of the recent programming job interviews, one of my readers was asked the question, how do you join two tables which are not related to each other? i.e. they don't have any common column? is it possible in SQL? My reader got confused because he only knows about INNER join and OUTER join which require a key column like dept_id which is the primary key in one table like Department and foreign key in another table like Employee. He couldn't answer the question, though he did tell them about you can select data from multiple tables by typing multiple table names in from clause using a comma. 

When to use TRUNCATE vs DELETE command in SQL?

 While working with the database we often need to delete data, sometimes to create more space, sometimes just remove all data to make the table ready for the next day's transaction, or sometimes just selectively remove stale data. SQL provides a couple of handy commands to remove data e.g. truncate, delete and drop. The last one is a bit different because instead of removing data it just deletes the table. What is the difference between truncate and delete command in SQL or when to use truncate vs delete is one of the most frequently asked SQL interview questions?

What is table scan, index scan, and index seek in SQL? Interview Question

 Hello guys, a good understanding of how the index works and how to use them to improve your SQL query performance is very important while working in a database and SQL and that's why you will find many questions based upon indexes on Programming Job interviews. One of such frequently asked SQL questions is the real difference between table scan, index scan, and index seek? which one is faster and why? How does the database chooses which scan or seek to use? and How you can optimize the performance of your SQL SELECT queries by using this knowledge. In general, there are only two ways in which your query engine retrieves the data, using a table scan or by using an index.

When to use EXISTS and NOT EXISTS in SQL? SQL Server Example

 Hello guys, if you are wondering how to use the IF EXISTS and NOT EXISTS in SQL then you are at the right place. Earlier, I have shared how to use GROUP BY, WHERE, and HAVING clause and in this tutorial I will share how to use exists and not exists clause in SQL. The IF EXISTS and NOT EXISTS commands in T-SQL are covered in depth in this article. When comparing data sets using subqueries, it also illustrates why EXISTS should be preferred over IN and NOT EXISTS over NOT IN. 

Difference between Primary key vs Unique key in a Table

The primary key and unique key are two important concepts in a relational database and are used to uniquely identify a row in a table. Both primary key and unique keys can identify a row uniquely but there is some subtle difference between them which we will see in this article. In fact, primary key vs unique is a popular SQL interview question along with classics like truncate vs delete and  How to manage transactions in a database, mostly asked to fresher and 2 to 3 years experience guys in any programming language.