The correlated subquery is one of the tricky concepts of SQL. It's similar to recursion in programming which many programmers struggle to understand, but like recursion, it also offers the unique capability to solve many SQL query based problems e.g. second highest salary problem where you need to compare one row of the table to another row. It gives you a different kind of power. The main difference between a regular, non-correlated and correlated subquery in SQL is in their working, a regular subquery just run once and return a value or a set of values which is used by outer query, but correlated subquery runs for each row returned by the outer query because the output of the whole query is based upon comparing the data returned by one row to the all other rows of the table. That's why it is also very slow and generally avoided until you don't know any other way to solve the problem.
4 Differences between ISNULL vs COALESCE in SQL Server
Even though both ISNULL() and COALESCE() function provides alternate values to NULL in SQL Server e.g. replacing NULL values with empty String, there are some key differences between them, and the key difference between them is that COALESCE() is a standard function but ISNULL() is a SQL Server specific, which means it's not guaranteed to be supported by other database vendors like Oracle, MySQL or PostgreSQL. But, perhaps the most important difference between them is that COALESCE is more flexible and powerful than ISNULL(). With ISNULL(), you can only provide one alternate value but with COALESCE you can provide more than one e.g. if col1 IS NULL then take value from column2, if that is NULL then take the default value. Btw, that's not the only differences, there are three key differences between ISNULL() and COALESCE() which we will explore in this article.
Difference between Primary and Foreign key in table - SQL
The database is a collection of tables and a table is the collection of rows or records. A primary key is the data to uniquely identify a row in the table. For example, if an Employee table has millions of rows and emp_id is a primary key then it can uniquely identify that row. You can use this primary key to retrieve (SELECT) the row, update the row and delete the row. In SQL, a table can have only one primary key but it can contain more than one columns. There are two types of primary key, a simple primary key, and a composite primary key. A simple primary key is made of just one column while a composite primary key is made of more than one columns. Primary key also enforces some constraint e.g. UNIQUE and NOT NULL, which means if you try to enter another record with the primary key already existing in the database than DBMS will reject it. Similarly, a primary key cannot be NULL, it must have some value. On the other hand, the foreign key is just the primary key of another table. In a relational database, two tables can be related to each other e.g. Employee and Department, those cases, the primary key of one table becomes the foreign key of another table. In this article, you will learn some key difference between the Primary and foreign key in a table. This is also one of the most common SQL Interview questions for 1 to 2 years experience programmers.
Subscribe to:
Posts (Atom)