Depends on your OS. For Chrome on macOS, it'll show a chrome-styled square box with an icon and a piece of text in the upper right hand corner, kinda like macOS notifications (but they look nothing like it). In general, web notifications (can) work the same way they do on your smart phone. They can arrive from an active tab as well as from a background service.
Safari (on macOS) will use the native macOS notification system to display notifications AFAIR. I have no idea what IE on Windows does.
JS service workers allow a JavaScript thread to run in the background while the browser chrome (the window) is closed. So, in theory, the browser doesn't need to be open. Also, notifications can be useful with an open browser too (or, multiple tabs with a tab in the background for example)
Safari (on macOS) will use the native macOS notification system to display notifications AFAIR. I have no idea what IE on Windows does.