CODEWITHSUNDEEP
sql
whitenysherman
whitenysherman

Reputation: 93

What is the difference between a query and transaction in SQL?

Studying SQL for first time and my teacher says that a query will always return some sort of information while a transaction will make data to be read or put into the database. I am confused because why do people then say INSERT is a query in SQL? Could someone please kindly explain to a beginner?

Upvotes: 9

Views: 15566

Answers (3)

Manan Desai
Manan Desai

Reputation: 81

A simple explanation would be like this Query is a single instruction like SELECT, UPDATE, DELETE etc and transaction is group of query to perform particular task

  • in case of money transaction operation, there are multiple tasks we need to perform like select account, verify balance, debit balance and credit balance so this 4 queries can be together an form a txn.

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1269873

A query usually refers to a SELECT statement, but may also refer to data modifications as well -- UPDATE, INSERT, DELETE, and MERGE are common DML ("data modification language") statements. Personally, I would call these four operations DML statements and reserve query for SELECT statements; I find this a useful distinction.

Databases implement a set of properties called ACID properties. These basically say that any SQL statement sees consistent data, regardless of what else is going on in the database. A simple way to think of about these is that all operations are serialized -- one statement completes before another begins, even in a multi-user environment. Serialization guarantees that operations are isolated from each other. In practice, serialization is very expensive and databases have other mechanisms to ensure integrity, but it is a useful abstraction when learning about the concepts.

What are transactions? Transactions are the mechanism that databases use to ensure the integrity of the data when data is being modified. Transactions often consist of one statement that modifies the data. But that is not necessary. In fact, you can have a complex set of data transformations within a single transaction.

The three key operations on a transactions are:

  • BEGIN TRANSACTION: Tell the database that a transaction is beginning. All changes within the transaction are invisible to other users while the transaction is "active".
  • COMMIT TRANSACTION: Make all the changes visible in the database. Conceptually, this happens instantaneously. The database guarantees that other users will not see partial changes to the data (i.e. enforces data integrity).
  • ROLLBACK TRANSACTION: Undo all the work. No other users ever see the changes.

Note: Many databases have options to weaken the data integrity. These can be useful for performance reasons for users who know what they are doing.

I should note that if all operations on a database are SELECT statements, then transactions are not necessary. The data is not changing, so the view of the data is consistent. So transactions are generally associated with DML statements.

Upvotes: 20

LenglBoy
LenglBoy

Reputation: 331

Working with SQL DBs you got both - transactions and queries. Here a simple explanation:

  • Queries are operations to CRUD (create (insert), update (set), read (select), delete (delete)) data inside a table.
  • The transaction is more or less the process of a single or multiple statements/queries/operations getting executed.

So this means a transaction contains some interactions and a query is an example of 1 kind of interaction. Important to know is that transactions are like buying stuff online. You operate and mark things you want to buy but only if you click "buy" you will finally get and pay for the stuff. For transactions, that means they need to be committed afterwards otherwise it will be undone again (rollback). Mostly your frameworks will commit changes automatically after each interaction and end the transaction with it.

Comment in case you got more questions.

Upvotes: 2

Related Questions